Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance of ISNULL or the AND/OR method in the WHERE clause Expand / Collapse
Author
Message
Posted Tuesday, May 24, 2011 4:22 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 8, 2014 4:54 PM
Points: 91, Visits: 164,680
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
Post #1113876
Posted Tuesday, May 24, 2011 4:33 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 3:47 AM
Points: 731, Visits: 2,042
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.
Post #1113884
Posted Tuesday, May 24, 2011 4:44 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 8, 2014 4:54 PM
Points: 91, Visits: 164,680
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
Post #1113893
Posted Tuesday, May 24, 2011 4:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
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
Kent user group
Post #1113900
Posted Tuesday, May 24, 2011 5:07 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 8, 2014 4:54 PM
Points: 91, Visits: 164,680
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
Post #1113902
Posted Tuesday, May 24, 2011 5:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Dont thank me , thank Gail (GilaMonster).





Clear Sky SQL
My Blog
Kent user group
Post #1113909
Posted Thursday, May 26, 2011 7:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 4,435, Visits: 6,336
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 at GMail
Post #1115444
Posted Thursday, January 17, 2013 4:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 7:15 PM
Points: 1, 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.
Post #1408675
Posted Friday, January 18, 2013 6:45 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 4,435, Visits: 6,336
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 at GMail
Post #1408880
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse