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.

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.

share|improve this question
    
Post your execution plan, please. –  Yaroslav Shabalin Mar 4 at 9:54

2 Answers 2

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

WHERE exists (SELECT 1 FROM LOVs WHERE value = STypeID )
share|improve this answer
    
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. –  Furkan Ekinci Mar 5 at 6:44
    
Could you post whole query? It would be much easier to analyse it. –  Franek Mar 5 at 7:23

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.

share|improve this answer

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.