May 6, 2013 at 6:14 am
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@InputValChar(1),
@ProdNrInt
SELECT@InputVal= '1',
@ProdNr= 10
-- Variables
DECLARE@PriceMoney
-- 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 =
(
SELECTCASE @InputVal
WHEN '1' THEN Price1
WHEN '2' THEN Price2
WHEN '3' THEN Price3
WHEN '4' THEN Price4
ELSE Price5
END
FROM#Products
WHEREProductNr = @ProdNr
)
DROP TABLE #Products
Thanks
May 6, 2013 at 6:32 am
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/
May 6, 2013 at 6:58 am
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.
May 6, 2013 at 12:01 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2013 at 2:28 am
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@InputValChar(1),
@ProdNrInt
SELECT@InputVal= '2',
@ProdNr= 10
-- Variables
DECLARE@PriceMoney
-- 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,
May 7, 2013 at 3:43 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2013 at 5:36 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply