RE: help me remove key lookups from the query below

SSC Guru

Points: 323504

January 8, 2013 at 11:30 am

we'd need to see the actual execution plan to see if you can make it faster;

I think what Sean Lange referred to, you want to try and convert the IN({200 parameters}) to a table you can join against instead;

i'm sure that is a performance hit, because the optimizer cannot use an index to search those parameters, or use statistics to know how unique they are. an in(defined list) gets converted into 200 OR statements, which requires a scan of all 150 values, instead of using an index to limit the results for you.

WHERE ENTITY_ID

IN (P0...P200)

if you could pre-populate the data into a temp table or table variable or something, i'd expect a performance boost.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!