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

Understanding CASE processing Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 6:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, 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
Post #1449684
Posted Monday, May 6, 2013 6:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
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/
Post #1449690
Posted Monday, May 6, 2013 6:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, 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
Post #1449700
Posted Monday, May 6, 2013 12:01 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1449826
Posted Tuesday, May 7, 2013 2:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, 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
Post #1450026
Posted Tuesday, May 7, 2013 3:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1450043
Posted Tuesday, May 7, 2013 5:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, 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
Post #1450073
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse