Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

enter image description here

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.

share|improve this question
    
Can you show the actual query? –  Aaron Bertrand Dec 14 '12 at 18:38
    
Unfortunately, I cannot. We have too much IP in this query to share it online. –  Jaxidian Dec 14 '12 at 18:38
1  
Uh, ok, well how about just the where clause? Also it shouldn't be too difficult to mask the proprietary parts, if you really think you've written SQL that nobody else has... –  Aaron Bertrand Dec 14 '12 at 18:39
1  
So why are you wanting/expecting a NCIX seek when you know additional columns will be required from the CIX? I'm lost :) –  Mark Storey-Smith Dec 14 '12 at 19:45
2  
Just because you could, doesn't mean you should :) Or, yes adding all the columns will result in a seek on that index but it isn't necessarily "better". Better is looking likely to be NCIX on SomeId, with nothing included. –  Mark Storey-Smith Dec 14 '12 at 19:52
show 8 more comments

1 Answer 1

up vote 4 down vote accepted

Edit: Following partial posting of query.

The query hasn't flipped to seek on the suggested NCI because of:

WHERE
    tblPriceRequestCurves.SomeID = SomeOtherID

Whatever SomeId is, it needs to feature in the index. Again, difficult to tell due to the obfuscation but probably as the sole indexed value, everything else INCLUDE.

Can't deduce from the query why your doing it this way but the repeated joining to tblPriceRequestCurves doesn't make much sense as it stands. Back to my earlier point, its all guesswork without the full picture.

Original:

The seek is on [oid], you've included this rather than make it part of the index. Can't be sure this is optimal for the entire query without seeing the query and execution plan but to turn this particular lookup into the non-clustered index seek you're looking for:

CREATE NONCLUSTERED INDEX [IxFK_TEST_tblPriceRequestCurveValues_ForQuoteViewing] 
ON [dbo].[tblPriceRequestCurveValues] ([oid] ASC)
INCLUDE ([DateForValue], [CurveValue], [BTUFactor], [FuelShrink])

Of course, this may be a really bad idea. All we've achieved is swapping a clustered index seek with a non-clustered index seek. Without seeing the full query, its impossible to say whether its the "right" thing to do.

My initial thought is that a clustered index seek (key lookup) for 500 rows in what appears to be an analytic type query is likely to be reasonable. You refer to this lookup as being slow but in what context? Is it just the highest estimated cost operator in the plan?

If ultimately this query is looking at data across a timespan, it may be that DateForValue would be a better choice as the leading key:

CREATE NONCLUSTERED INDEX [IxFK_TEST_tblPriceRequestCurveValues_ForQuoteViewing] 
    ON [dbo].[tblPriceRequestCurveValues] ([DateForValue] ASC, [oid] ASC)
    INCLUDE ([CurveValue], [BTUFactor], [FuelShrink])

If you're always looking at data for a single [oid], perhaps they need to flip to [oid] ASC, [DateForValue] ASC.

This is optimisation through the eye of a needle. Query optimisation usually needs to assess the query and execution plan as a whole.

share|improve this answer
    
To answer a few of your questions: This query pulls back ~30k records. It's the highest estimated cost operator in the plan. The entire query takes ~30 seconds to run. These columns that you're saying to include (that I was keying on) are being selected only, not filtered. With your suggested index, it still performs a key lookup on the clustered index. –  Jaxidian Dec 14 '12 at 18:46
2  
@Jaxidian We're trying to understand the seek predicate. Hopefully your masking of the query won't mask that detail away. –  Aaron Bertrand Dec 14 '12 at 18:49
3  
Can't tell, too much obfuscation. You haven't included enough information to make that call. Simplest is going to be to try it and see. –  Mark Storey-Smith Dec 14 '12 at 19:17
2  
@Jaxidian there is no "obviously much better" between <a> and <b>. There is often a whole lot of "it depends." –  Aaron Bertrand Dec 14 '12 at 20:14
1  
As a starting point, looking at execution plans in SQL Sentry Plan Explorer can be a lot more enlightening than in Management Studio. I point out how some of these issues are highlighted in a demo kit I wrote last year (which you can also use for learning the tool to some degree). Disclaimer: I work for SQL Sentry. May also want to grab Grant Fritchey's free e-book: sqlservercentral.com/articles/books/65831 –  Aaron Bertrand Dec 14 '12 at 20:23
show 6 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.