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

What is the Difference ? Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 2:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:04 AM
Points: 48, Visits: 130
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






Post #1521788
Posted Wednesday, December 11, 2013 3:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,197, Visits: 9,148
Do you have any actual query plans for both statements?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1521799
Posted Wednesday, December 11, 2013 3:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 14,788, Visits: 27,265
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
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 #1521800
Posted Wednesday, December 11, 2013 3:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:04 AM
Points: 48, Visits: 130
Hi Guys,

Thanks for the replies,

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

Thanks,
Prabhu
Post #1521801
Posted Wednesday, December 11, 2013 4:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:04 AM
Points: 48, Visits: 130
please find the attachment

  Post Attachments 
Execution_plan_CASE_01.sqlplan (4 views, 64.23 KB)
Execution_plan_Direct_01.sqlplan (2 views, 87.21 KB)
Post #1521816
Posted Wednesday, December 11, 2013 5:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 14,788, Visits: 27,265
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
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 #1521844
Posted Wednesday, December 11, 2013 5:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:04 AM
Points: 48, Visits: 130
Hi G.F,

yes,

it works as Charm..

Thanks,
Prabhu
Post #1521850
Posted Wednesday, December 11, 2013 6:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 14,788, Visits: 27,265
prabhu.st (12/11/2013)
Hi G.F,

yes,

it works as Charm..

Thanks,
Prabhu


Which, the cost threshold change?


----------------------------------------------------
"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 #1521855
Posted Wednesday, December 11, 2013 10:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:04 AM
Points: 48, Visits: 130
thanks for your curiousness, I think it is around 1.something, (comparitively much better than earlier)
Post #1522147
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse