0

I've noticed in my system, whenever a non-clustered index is used in a query that has to also do a key lookup to get the additional fields being selected, it's faster for me to instead do two queries.

The first with the non-clustered index inserting only the key field into a temp table (so no key lookup is performed) and the second using that temp table to join back to the original table to filter it down on the key and then select the fields I need.

I'm typically querying tables with hundreds of millions to tens of billions of rows when I notice this. I'm not sure if it can be related to the fact that I'm eliminating the key lookup when the table is first loaded into memory and instead I'm inserting the key into a temp table so that the subsequent field lookup query occurs between two tables already in memory?

The difference in time I'll see is usually significant too, e.g. on the order of minutes.

Improve this question
3
  • It shouldn't be faster, but you are essentially forcing a particular execution plan. So compare the plans and see if the single query makes a poor choice of join style.
    – David Browne - Microsoft
    Commented Jan 30, 2020 at 18:13
  • @DavidBrowne-Microsoft I wouldn't have thought so either, but I don't really see anything jumping out in the execution plans. The queries can be as simple as a select for a single field in one table that doesn't have an index to cover that field. Is it possible the answer is in the IO statistics though?
    – J.D.
    Commented Jan 30, 2020 at 20:30
  • 1
    If you have a specific example then please post the execution plans
    – Martin Smith
    Commented Jan 31, 2020 at 9:32

2 Answers 2

Reset to default
1

I can think of a couple of cases where this approach might be beneficial.

  1. Sometimes you can end up with an execution plan which does a load of lookups for rows that are then ultimately filtered out downstream (I've noticed this especially with pagination queries). If you only store the rows post filtering there will be fewer "manual lookups" to resolve. This scenario can generally be addressed with a self join and does not need intermediate materialisation.
  2. Lookups always use nested loops. The "manual lookup" might use a different join type - the cardinality estimates for rows to lookup will be spot on when materialised and may differ from the original estimates encouraging this.

For the case where neither of the above apply (and you are just materialising into a temp table and getting the same number of nested loops lookups as you would have got without this step and not benefiting from improved cardinality estimates) I would expect this to generally be slower than the original query without the intermediate step (as on the face of it you are doing the same work with some additional overhead added) but haven't tested this.

Improve this answer
0

Is it possible with your table design to add the columns coming from the key lookup as INCLUDED columns in the non-clustered index?

Improve this answer
4
  • I know I can do that for index tuning, but in a lot of cases I'm not really able to make those changes.
    – J.D.
    Commented Jan 30, 2020 at 22:52
  • Unless it is a 3rd party app where changing indexes can break support agreements, then if there is any case where changing an index can improve performance then I can't see why there would be any reason not to implement it. Really what you are describing above is a query tuning exercise anyway.
    – Martin Cairney
    Commented Feb 2, 2020 at 2:36
  • I don't disagree with index tuning when applicable, but I just started a new job where I don't have the authorization (yet) to make many index changes myself. Additionally it's difficult for us to make DDL changes because we have almost no maintenance window (the application can't go offline and can't really afford to be delayed) and the tables are pretty big (hundreds of millions to tens of billions of rows). Also these queries I'm turning are generally ad-hoc or low use that wouldn't really warrant structural changes to the database by themselves.
    – J.D.
    Commented Feb 2, 2020 at 6:22
  • But I have ran into a few cases so far where to reduce read locks and query time while ad-hoc reporting on the data, instead of letting the single query do a key lookup, it's a lot faster to break it into two queries as stated in my original post.
    – J.D.
    Commented Feb 2, 2020 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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