We have this complicated query that I'm trying to make "better" until we move it to pull from a data warehouse. I need a solution that's "good enough" for now and I think I'm about 2-3 indexes away from making that happen. I'm stuck on this part, however.
I'm specifically targeting this part of my Execution Plan:
That table originally had only 2 indexes: the Clustered PK index (that this shows it doing the Key Lookup on) and another FK index on a column not referenced here. Given that this heavy query always needs these add'l columns (DateForValue [datetime], CurveValue [float], BTUFactor [float], and FuelShrink [float]), I thought a covering index was the obvious solution here to remove the (slow) Key Lookup being performed here. So I added the following covering index:
CREATE NONCLUSTERED INDEX [IxFK_TEST_tblPriceRequestCurveValues_ForQuoteViewing] ON [dbo].[tblPriceRequestCurveValues] ( [DateForValue] ASC, [CurveValue] ASC, [BTUFactor] ASC, [FuelShrink] ASC ) INCLUDE(oid)
However, even after adding this index, it seems the query is still doing the Key Lookup.
Am I missing something obvious here or is this the right idea and I just have a problem elsewhere? Note that all statistics and indexes have been refreshed and this isn't THAT highly dynamic of a table but it is approaching ~1M records.
A simplified version of this query, focusing on this table of interest, is as follows. Nothing I removed references the PrimaryTableOfInterest.
SELECT * FROM (query1) UNION ALL ( SELECT Other table columns, PrimaryTableOfInterestForNow.oid, PrimaryTableOfInterestForNow.PriceRequestCurveID, PrimaryTableOfInterestForNow.DateForValue, PrimaryTableOfInterestForNow.CurveValue, PrimaryTableOfInterestForNow.BTUFactor, PrimaryTableOfInterestForNow.FuelShrink FROM ( SELECT Other table columns, tblPriceRequestCurves.PriceRequestCurveID AS MyForeignKey FROM tblPriceRequestCurves INNER JOIN other stuff unrelated to PrimaryTableOfInterestForNow WHERE tblPriceRequestCurves.SomeID IS NOT NULL ) INNER JOIN tblPriceRequestCurveValues AS PrimaryTableOfInterestForNow ON MyForeignKey = PrimaryTableOfInterestForNow.PriceRequestCurveID WHERE tblPriceRequestCurves.SomeID = SomeOtherID ) UNION ALL (query3) ORDER BY xxxx
I'm working with each of the 3 portions of the UNION ALL independent of one another and the other two parts are nice and speedy and executing this third of the unions either by itself or in the union performs similarly (i.e. ~30 seconds). So the UNION isn't a factor but I included it just for thoroughness sake.