Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understanding CASE processing


Understanding CASE processing

Author
Message
Dennis Post
Dennis Post
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 547
Hi,

I found an SP that generates multiple index seeks from 1 case statement.
By the looks of it each WHEN THEN is run regardless if the WHEN is true or not.

Do CASE statements always behave like this or can they be forced to only execute when true thus skipping the rest.?


---------------------------------------------------
-- Test 1 CASE Multiple COLUMNS
---------------------------------------------------
CREATE TABLE #Products
(
ProductNr Int PRIMARY KEY,
Price1 Money,
Price2 Money,
Price3 Money,
Price4 Money,
Price5 Money
)

-- Parameters
DECLARE @InputVal Char(1),
@ProdNr Int
SELECT @InputVal = '1',
@ProdNr = 10

-- Variables
DECLARE @Price Money

-- Execution plan returns multiple index seeks
SET @Price =
(
CASE
WHEN @InputVal = '1' THEN (SELECT Price1 FROM #Products WHERE ProductNr = @ProdNr)
WHEN @InputVal = '2' THEN (SELECT Price2 FROM #Products WHERE ProductNr = @ProdNr)
WHEN @InputVal = '3' THEN (SELECT Price3 FROM #Products WHERE ProductNr = @ProdNr)
WHEN @InputVal = '4' THEN (SELECT Price4 FROM #Products WHERE ProductNr = @ProdNr)
ELSE (SELECT Price5 FROM #Products WHERE ProductNr = @ProdNr)
END
)

-- Execution plan returns 1 index seek
SET @Price =
(
SELECT CASE @InputVal
WHEN '1' THEN Price1
WHEN '2' THEN Price2
WHEN '3' THEN Price3
WHEN '4' THEN Price4
ELSE Price5
END
FROM #Products
WHERE ProductNr = @ProdNr
)

DROP TABLE #Products



Thanks



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2224 Visits: 6079
The procedure's execution plan has to generate a plan for all the DML statement that can be used regardless of the value of parameters and variables. Since the first case statement has 4 different select statement, the query plan for this procedure should include query plan for each one of those queries. During runtime only one of the select statements will run. The second case statement has only one select statement that each time will get the same records, but each time will show a different column. For this type of statement the server can create a single query plan, because regardless of the columns that it will show at the end, it will use the same algorithm in order to get the data.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Dennis Post
Dennis Post
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 547
Hi,

Thanks for the explaination.

So I don't need to worry about a big performance hit?
Just need to remove the extra WHEN THEN ELSEs when tuning so that the results are more acurate?

At the moment the plan is telling me that this part of the SP takes up 70% and each index seek in it takes 10%. When in reality the 70% should be considered much lower.



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17605 Visits: 32267
Two points.

Those costs are estimated costs. They don't necessarily relate in any way to reality. You really can't compare two queries through the costs. Not realistically.

Having multiple queries in a statement like this can be problematic if the parameter values passed can result in the optimizer picking different plans. This pattern is fine, but instead of having the query inside the batch, call a procedure that contains the query. That way it's plan will only be generated when it gets called and only with the parameter values used.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Dennis Post
Dennis Post
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 547
Then which values are best used for comparing queries?
SET STATISTICS IO ON & SET STATISTICS TIME ON?

Using IF statements also seems to generate 1 plan. Is this what you wish achieve with the SP call?

This test is with different tables instead of columns.
---------------------------------------------------
-- Test 2 CASE Multiple TABLES
---------------------------------------------------
CREATE TABLE Products1 (ProductNr Int PRIMARY KEY, Price Money)
CREATE TABLE Products2 (ProductNr Int PRIMARY KEY, Price Money)
CREATE TABLE Products3 (ProductNr Int PRIMARY KEY, Price Money)
CREATE TABLE Products4 (ProductNr Int PRIMARY KEY, Price Money)
CREATE TABLE Products5 (ProductNr Int PRIMARY KEY, Price Money)
GO

--Parameters
DECLARE @InputVal Char(1),
@ProdNr Int
SELECT @InputVal = '2',
@ProdNr = 10

-- Variables
DECLARE @Price Money

-- Execution plan returns 1 index seek
IF @InputVal = '1'
SET @Price = (SELECT Price FROM Products1 WHERE ProductNr = @ProdNr)
ELSE IF @InputVal = '2'
SET @Price = (SELECT Price FROM Products2 WHERE ProductNr = @ProdNr)
ELSE IF @InputVal = '3'
SET @Price = (SELECT Price FROM Products2 WHERE ProductNr = @ProdNr)
ELSE IF @InputVal = '4'
SET @Price = (SELECT Price FROM Products2 WHERE ProductNr = @ProdNr)
ELSE
SET @Price = (SELECT Price FROM Products2 WHERE ProductNr = @ProdNr)

SELECT @Price
GO

DROP TABLE Products1
DROP TABLE Products2
DROP TABLE Products3
DROP TABLE Products4
DROP TABLE Products5



Cheers,



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17605 Visits: 32267
Yeah, pretty much. The execution time and the resources used are reasonably accurate measures. The estimated costs are just that, estimates. They are the only numbers we have within execution plans, but they really can't be trusted, especially when comparing one plan to another.

And no, instead of having a query inside the IF construct, execute a stored procedure. That separate stored proc will generate it's own plan when it gets called and not before.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Dennis Post
Dennis Post
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 547
Great. Thanks for clearing that up for me.



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
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