SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance of ISNULL or the AND/OR method in the WHERE clause


Performance of ISNULL or the AND/OR method in the WHERE clause

Author
Message
Thinknook
Thinknook
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 164990
Hi,

Am running into a very weird performance issue, basically I have a bunch of reporting queries that uses the ISNULL function in the WHERE clause.

The WHERE clause for the query looks like this:


WHERE
CustomerID = ISNULL(@CustomerID,CustomerID)
AND (DateStamp >= @StartDate AND DateStamp <=@EndDate)
AND AccountMappingID = ISNULL(@AccountMappingID,AccountMappingID)
AND CampaignMappingID= ISNULL(@CampaignMappingID,CampaignMappingID)
GROUP BY
ProviderName,
CampaignName



and the clustered index on the table looks like this:

[CustomerID] ASC,
[DateStamp] ASC,
[AccountMappingID] ASC,
[CampaignMappingiD] ASC,
[AccountName] ASC,
[ProviderName] ASC,
[CampaignName] ASC




Now based on what I've been reading, the ISNULL is obviously a function, and so SQL cant predict the outcome of the function pre running it, and so it does not utilize the index for CustomerID, AccountMappingID, CampaignMappingID columns, which leads to a table SCAN (which is what am seeing).

I have changed the WHERE clause to:

WHERE
(CustomerID = @CustomerID OR @CustomerID IS NULL)
AND (DateStamp >= @StartDate AND DateStamp <=@EndDate)
AND AccountMappingID = ISNULL(@AccountMappingID,AccountMappingID)
AND CampaignMappingID= ISNULL(@CampaignMappingID,CampaignMappingID)



Now I expect that there should be a SEEK on CustomerID and DateStamp. but in reality am still getting a SCAN upon calling the stored procedure.

The only time i dont get a SCAN is if I removed the OR @CustomerID IS NULL in the last WHERE clause, then it would perform a SEEK on CustomerID and DateStamp.

Anyone experience a similar scenario? is there an alternative to using either CustomerID = ISNULL(@CustomerID, CustomerID) or CustomerID = @CustomerID OR @CustomerID IS NULL that might actually lead to a SEEK rather than a SCAN?

I even tried using CASE statement, but no joy!

Any input is much appreciated as this is doing my head in, if more information is required to understand the environment/query let me know.

Cheers!
/>L

-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers.

I believe in Codd
... and Thinknook is my Chamber of Understanding
Latheesh NK
Latheesh NK
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1671 Visits: 2164
I faced such issues sometimes back. I had separated the queries.

As long as you have many condition, it would be great if you can go for a dynamic parameterized query execution.
Thinknook
Thinknook
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 164990
Aye separating the query seems to be the way forward, although it just feels "dirty". I mean we're talking 11 reporting queries, each will have 3 ISNULL parameters, thats 33 queries just to get some real basic functionality.

I wonder why is this happening interms of the SQL engine itself, it seems very counter-intuitive for essentially a simple problem:
"I want to return either a filtered result set (while utilizing the index), or all the result set (doing a table SCAN)"... weird!

I would rather not go down the dynamic SQL route, just interms of maintenance/debugging/administering/security etc..

Thanks for the prompt reply!!
/>L

-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers.

I believe in Codd
... and Thinknook is my Chamber of Understanding
Dave Ballantyne
Dave Ballantyne
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7262 Visits: 8370
Hi , see this page for a breakdown of your options

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/



Clear Sky SQL
My Blog
Thinknook
Thinknook
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 164990
You are a genius!!

That article made A LOT of sense, in terms of whats actually happening in the background as well as how to get around the issue.

Thank you very much for your help.

-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers.

I believe in Codd
... and Thinknook is my Chamber of Understanding
Dave Ballantyne
Dave Ballantyne
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7262 Visits: 8370
Dont thank me , thank Gail (GilaMonster).



Clear Sky SQL
My Blog
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32765 Visits: 8677
This is a VERY common scenario - just saw it yet again at a client earlier this week. If you have many NULLable variables and/or tables that you join to just to check existence (i.e. you don't also pull data from them) dynamic SQL is BY FAR the way to go here. For those tables where the variable you are joining with are NULL, you can completely exclude a hit on that table! I routinely get 4-5 ORDERS OF MAGNITUDE performance gains from this type of refactor. IFs with nested sprocs is another option, but I feel it is more difficult to maintain personally. If you do use dynamic SQL please ensure you guard against SQL Injection.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jeff-569323
Jeff-569323
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 10
Dave Ballantyne (5/24/2011)
Hi , see this page for a breakdown of your options

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/


Confirming great discussion of the causes of unacceptable performance and the available workarounds at this link. I inherited a "Catch-all query" stored procedure during migration from SQL 2000 to SQL 2008. It includes 5 ISNULL tests in the WHERE clause.

Execution time on the original design platform (2000) was about 5 sec.

Migrating as-is to 2008 resulted in 55 sec. execution time!!!

Refactored using the dynamic SQL approach covered above, and execution time of the query with no other design changes is < 1 sec.

Many thanks to Dave. And Gail.
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32765 Visits: 8677
1) Note this is an almost 2-year-old post.

2) When you migrated to 2008, did you update ALL statistics with a FULL SCAN?? That is a MANDATORY step in the upgrade process that is often missed and leads to HORRIBLE performance!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search