September 23, 2013 at 10:16 am
LinksUp (9/23/2013)
Your code is indeed more efficient!
I just ran all 3 queries and checked the Execution plan on each one and I am only seeing 2 Table Scans on both my solution and the cte. Whereas your solution does only have 1. What are you looking at to determine 3 Table Scans?
(See attached jpg for execution plan)
When I run all three versions with SET STATISTICS IO ON, I get this:
=========================================
Prepare sample data:
(14 row(s) affected)
(1 row(s) affected)
=========================================
Subselect version:
(2 row(s) affected)
Table '#3B219CFC'. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
=========================================
CTE version:
(2 row(s) affected)
Table '#3B219CFC'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
=========================================
CASE version:
(2 row(s) affected)
Table '#3B219CFC'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
Your subselect has to be performed once for each distinct value of party_code, even though you see only one table scan operator in the execution plan. If you look at the execution plan, you'll see that once branch of the logical tree shows a table scan of [party_payments] [t2] feeding a stream aggregate that computes the SUM in the subselect. If you look at the XML of the plan (by opening the attached .sqlplan with a text editor), you'll see that the table scan is actually performed twice, once for each distinct value of party_code. Here's is the RunTimeInformation node of the RelOp node for the stream aggregrate (NodeID = "15"):
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="0" ActualExecutions="2" />
</RunTimeInformation>
and the RunTimeInformation node of the RelOp node for the supporting table scan (NodeID = "16") - you see it's a child node of the stream aggregate RelOp:
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="2" ActualExecutions="2" />
</RunTimeInformation>
Note the value of the ActualExecutions attribute in each case: "2".
Add these two scans to the table scan of [party_payments] [t1] and you have 3 scans altogether.
You can test this by adding rows with additional distinct values of party_code - you'll see an additional scan for each additional distinct value.
If you're familiar with XPath notation, here are the XPaths to those nodes:
The stream aggregate:
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/ComputeScalar/RelOp/NestedLoops/RelOp[2]/ComputeScalar/RelOp
and the table scan:
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/ComputeScalar/RelOp/NestedLoops/RelOp[2]/ComputeScalar/RelOp/StreamAggregate/RelOp
Jason Wolfkill
September 23, 2013 at 10:27 am
And duh, of course you can see the number of executions of a node in an execution plan displayed as Number of Executions in the Properties that pop up when you hover over the node. No need to scrutinize the XML - I just like to dig through it sometimes because you can find things there that you can't see in SSMS's graphical display.
Jason Wolfkill
September 23, 2013 at 10:33 am
wolfkillj (9/23/2013)
And duh, of course you can see the number of executions of a node in an execution plan displayed as Number of Executions in the Properties that pop up when you hover over the node. No need to scrutinize the XML - I just like to dig through it sometimes because you can find things there that you can't see in SSMS's graphical display.
Thanks for the reminder. I never looked beneath the surface of the execution plan to see the actual number of executions.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 23, 2013 at 10:41 am
LinksUp (9/23/2013)
wolfkillj (9/23/2013)
And duh, of course you can see the number of executions of a node in an execution plan displayed as Number of Executions in the Properties that pop up when you hover over the node. No need to scrutinize the XML - I just like to dig through it sometimes because you can find things there that you can't see in SSMS's graphical display.Thanks for the reminder. I never looked beneath the surface of the execution plan to see the actual number of executions.
It's easy to overlook such things with only a small amount of test data (that you had to create yourself, no less). Running the code on a large data set would have brought the issue to light, I'm sure. Thanks for being the one to educate the OP about the need to provide DDL and test data.
Jason Wolfkill
September 23, 2013 at 10:50 am
wolfkillj (9/23/2013)
Thanks for being the one to educate the OP about the need to provide DDL and test data.
What kind of torques my wrench is that the OP posted another question today with the same deficiencies; "I need a query that does _________."
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 24, 2013 at 9:14 am
select party_code,sum(inst_amt) as Total_instamt,
sum(case when paid_date < '27/11/2012' then paid_amt else 0 end) as Total_Paidamt
from @table1
group by party_code
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply