0

I use a static list for some queries WHERE clause like

WHERE STypeID IN ('12', '13', '14', '265')

This list will be changed in the future; new values will be added. When list changed, I don't want to check all queries which has this list to change, want to control this list from 1 place; so I created a table with two columns; Key and Value, inserted my static values into table.

      Key             Value

SupportedTypeID        12  
SupportedTypeID        13  
SupportedTypeID        14  
SupportedTypeID        265

I modified my where clause like

WHERE STypeID IN (SELECT Value FROM LOVs WHERE Key LIKE 'SupportedTypeID')

but it causes performance issue; queries results come too late.

1
  • Post your execution plan, please. Commented Mar 4, 2014 at 9:54

2 Answers 2

1

In most cases replacement IN clause with EXISTS clause gives performance boost. Try this:

WHERE exists (SELECT 1 FROM LOVs WHERE value = STypeID )
2
  • This is not a completely correct answer but it changed performance little bit. There were left outer join (it made by using + sign) in my query, I replaced it like "left outer join" and now performance looks like acceptable. Commented Mar 5, 2014 at 6:44
  • Could you post whole query? It would be much easier to analyse it.
    – rtbf
    Commented Mar 5, 2014 at 7:23
0

WHERE STypeID IN (SELECT Value FROM LOVs WHERE Key LIKE 'SupportedTypeID')

Quit using the IN clause, and simply JOIN your tables. Use AND in your ON clause to filter the joined table's rows down to the ones you're interested in.

INNER JOIN LOVs
ON LOVs.Value = STypeID
AND LOVs.Key = SupportedTypeID

Also, for your performance tuning, use SQL Server Profiler on the SQL:BatchCompleted and RPC:Completed events, and look at the Reads, Writes, CPU, and Duration columns.

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.