I have an admittedly-complex query that I'm optimizing and I'm stuck on the next step. I have pinpointed the bulk of my problem to one part of my query that's doing the following Key Lookup but I'm confused both as to why it's doing this and what it's so slow. I'm not even referencing the PK in that table. I'm doing:
WHERE EXISTS (SELECT 1 FROM tblPriceTierQuantities [Extent8] INNER JOIN xxxx ON xxx=Extent8.AccountRateScheduleID WHERE Extent8.PriceTierID=tblPriceTiers.oid)
The details on the execution plan show as this:
That shows up as the only thing taking significant time to execute. Indeed, if I simply comment out that
WHERE Extent8.PriceTierID=tblPriceTiers.oid clause, then the query is as speedy as I want it to be. The thing is, this lookup seems to be pointing to Extent8's oid and not tblPriceTier's oid field.
I'm confused why it's hitting the clustered index on that table because of this query. Nothing else in this query hits that table at all. Can you explain why this clustered lookup exists and also why it might be so slow? The ONLY columns ever referenced on this tblPriceTierQuantities table are
oid which is the only column in this clustered index.