What is the Difference ?

Question

What is the Difference ?

prabhu.st

SSCrazy

Points: 2552
More actions
December 11, 2013 at 2:52 am

#301361

Hi,
Recently I got to execute one query which comprise the CASE statement as below, though the condition within the case statement can be executed even directly (without CASE), but I found some performance Increase in the Direct method and vice versa in CASE.
SampleCode:
------------
MainQuery
----------
SELECT
T1.Column1 AS TC1,
T5.Column1 AS TC2
/* The Staging Table Details.*/
FROM StagingDB.dbo.Table1 T1
JOIN StagingDB.dbo.SomeMappingTable1 T2 ON T1.Column=T2.Column
JOIN StagingDB.dbo.SomeMappingTable2 T3 ON T1.Column=T3.Column
LEFT JOIN DB1.dbo.SomeRelatedTable_To_T2 T4 ON T2.Column=T4.Column
LEFT JOIN DB1.dbo.SomeRelatedTable_To_T3 T5 ON T3.Column=T5.Column
/* The Production Table Details.*/
JOIN ProdDB.dbo.Table1 T6 ON T1.Column=T6.Column
JOIN ProdDB.dbo.SomeMappingTable1 T7 ON T6.Column=T7.Column
LEFT JOIN DB1.dbo.SomeRelatedTable_To_T7 T8 ON (T7.Column=T8.Column AND T4.Column=T8.Column)
LEFT JOIN DB1.dbo.SomeRelatedTable_To_T7 T9 ON (T7.Column=T9.Column AND T4.Column=T9.Column)
"WHERE" Clause to be Attached with the Above Query
---------------------------------------------------
Type 1: (Direct Method)
WHERE T1.IsDefault != T6.IsDefault /* QueryCost = 13.051768 */
Type 2: (CASE Method)
WHERE (CASE
WHEN T1.IsDefault != T6.IsDefault THEN 1
ELSE 0
END) = 1 /* QueryCost = 8.002340 */
My Question:
what is the reason for the difference in performance, where the "core condition" is same with both options.
Thanks in Advance.
Prabhu

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Do you have any actual query plans for both statements?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

Without seeing the execution plan I couldn't say for sure, but I'll bet the plan for the statement without the CASE is more accurate to your indexes and statistics while the CASE is probably not. It has to do with performing functions that make your plan less determinate and more complicated, the CASE, leading to possible timeouts in the optimizer or just plain old bad plan choices. With the execution plans we could say more.

Side note, be extremely cautious comparing plan costs between plans. They don't directly correlate to a difference in performance.

"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

prabhu.st SSCrazy Points: 2552 More actions · Answer 3

Hi Guys,

Thanks for the replies,

I would give you the Execution plan (XML File), give me couple of minutes.

Thanks,

Prabhu

prabhu.st SSCrazy Points: 2552 More actions · Answer 4

prabhu.st

SSCrazy

Points: 2552

December 11, 2013 at 4:06 am

#1672554

please find the attachment

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

Obviously you're getting two different plans with two different execution times. I'd say the principal difference is in the hash keys residual that adds the CustomerSets data to the combined data from the other two tables. Both plans have a hash join along with a residual probe (a secondary filtering criteria). The case probe looks like this:

[GlobalizationStaging_DCS_SIT].[dbo].[PCE_CustomerSets].[CustomerSet] as [pcs].[CustomerSet]=[Globalization_DCS_SIT].[dbo].[CustomerSets].[CustomerSet] as [cs].[CustomerSet] AND CASE WHEN [GlobalizationStaging_DCS_SIT].[dbo].[PCE_CustomerSetSegmentMap].[IsDefault] as [pcsm].[IsDefault]<>[Globalization_DCS_SIT].[dbo].[CustomerSets].[IsDefault] as [cs].[IsDefault] THEN (1) ELSE (0) END=(1)

And the other probe looks like this:

[GlobalizationStaging_DCS_SIT].[dbo].[PCE_CustomerSets].[CustomerSet] as [pcs].[CustomerSet]=[Globalization_DCS_SIT].[dbo].[CustomerSets].[CustomerSet] as [cs].[CustomerSet] AND [GlobalizationStaging_DCS_SIT].[dbo].[PCE_CustomerSetSegmentMap].[IsDefault] as [pcsm].[IsDefault]<>[Globalization_DCS_SIT].[dbo].[CustomerSets].[IsDefault] as [cs].[IsDefault]

I'm pretty sure that simpler and cleaner probe works faster. And the evidence is there to support it.

At a very fundamental logical level these things are the same, right? But at a programming level, the CASE statement is trying to tell SQL Server what to do rather than tell it what to get. Always think of your T-SQL as a means of telling SQL Server what to get, not how to get it.

Side note, the estimated costs on these queries is very load 10 & 8. I'll be they don't really benefit from parallelism. Try setting your cost threshold for parallelism to a higher number (say, 35) and rerun the non-CASE query to see if you get an improvement.

"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