0

I have a query where there are numerous values passed in

This is a heavily simplified version of the query

SELECT Id, Name, <other cols..>
FROM Table
<various joins etc.>
WHERE Id in (1,2,3,4,5,6,7....1000,1001,1002 etc.)

I have noticed that when passing in fewer values the query is quick and uses the required index to return the data, however after a certain amount of values the query uses a constant scan and a hash table to join to the main result set which is killing the performance.

Is there a better way of returning this data?

Improve this question
3
  • 1
    I suggest you read this. – SqlWorldWide May 16 '17 at 12:52
  • Seeks escalate to scans when the optimizer thinks it's cheaper. For large numbers of seek values, it often is cheaper to scan. You can force a seek if you want to test it: FROM Table WITH(FORCESEEK). – Steven Hibble May 16 '17 at 17:14
  • What is the definition of "table"? What is the definition of the index? What is a query plan for a fast query? What is a query plan for a slow query? How long do the queries take to finish? What is your target response time? You can use brentozar.com/pastetheplan – Joe Obbish May 17 '17 at 0:21
1

Put your values into #tmp table and join it with the rest of your query

Improve this answer
1
  • 1
    Can you explain why your suggestion could help with performance? – Joe Obbish May 17 '17 at 0:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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