Sargable alternative to find orphaned records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sargable alternative to find orphaned records

Author

Topic

ltgrady
Starting Member

9 Posts

Posted - 2011-11-17 : 00:43:09

We have a stored procedure that is a pig. It's grabbing a bunch of products from our database but we have to determine if those products are authorized or not. Authorizations are kept in a different table. So basically it looks like this....

SELECT productName, productID, ......
productPrice,
(CASE
WHEN authID IS NULL THEN 0
ELSE 1
END) AS bARAuthorized)

FROM tblProducts
LEFT JOIN tblAuthorizations
ON........

This is actually a much bigger query with lots more fields and a few tables but this is the important part. When I run the query it takes 12-18s to execute. If I remove the CASE and just return the authID value it runs in 1-3s.

Is there are sargable way to do this query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 00:54:47

Do you've required indexes on tblAuthorizations ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-11-17 : 05:12:41

Just a suggestion.Try this


Select *,(CASE 
WHEN authID IS NULL THEN 0
ELSE 1
END) AS bARAuthorized From 
(
SELECT productName, productID, ......
productPrice,
authID

FROM tblProducts
LEFT JOIN tblAuthorizations
ON........
)T

PBUH

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-17 : 10:34:16

The CASE expression should not affect your query that much. However, if that column being used in the case expression is requireing a lookup, it's possible that is causing performance issues. Did you look at the Actual Query Plan to see what is going on?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources