May 24, 2011 at 4:22 am
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[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
May 24, 2011 at 4:33 am
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.
May 24, 2011 at 4:44 am
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[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
May 24, 2011 at 4:57 am
Hi , see this page for a breakdown of your options
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
May 24, 2011 at 5:07 am
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[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
May 24, 2011 at 5:14 am
Dont thank me , thank Gail (GilaMonster).
May 26, 2011 at 7:16 am
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
January 17, 2013 at 4:59 pm
Dave Ballantyne (5/24/2011)
Hi , see this page for a breakdown of your optionshttp://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.
January 18, 2013 at 6:45 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply