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 ««123»»

How to Write Query for the Below Requirement? Expand / Collapse
Author
Message
Posted Monday, September 23, 2013 3:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 7:24 PM
Points: 25, Visits: 40
Thank u So Much Sir.
It works for me.


I am New to This forms.So I don't know how to Post.


Thank u so much for all your patient replies.


Thanks
Post #1497310
Posted Monday, September 23, 2013 8:09 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:39 AM
Points: 101, Visits: 317
Using CTE's, the logic of certain queries can be broken down in to simpler steps:

WITH pd(zParty_Code, zSumPaid_Amt)
AS (
SELECT party_code, SUM(paid_amt)
FROM dbo.tPartyData
WHERE paid_date < '11/27/2012'
GROUP BY party_code
)

SELECT party_code
, SUM(inst_amt) AS Total_Inst_Amt
, zSumPaid_Amt AS Total_Paid_Amt
FROM dbo.tPartyData
JOIN pd
ON party_Code = zParty_Code
GROUP BY party_code, zSumPaid_Amt

Post #1497400
Posted Monday, September 23, 2013 8:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:43 AM
Points: 344, Visits: 1,322
perireddy.arikatla (9/23/2013)

I am New to This forms.So I don't know how to Post.


Please don't use that as an excuse. You were told how to post 3 times, twice by myself (with an example) and once by Mr. Moden, who provided a link to an article on "How to Post".





__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1497431
Posted Monday, September 23, 2013 8:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:21 PM
Points: 1,210, Visits: 2,512
LinksUp (9/22/2013)
perireddy.arikatla (9/22/2013)


When i Run the query without giving party_code in where clause
i.e

SELECT party_code, sum(inst_amt) as total_insts,
(Select Sum(paid_amt) as total_paid
From party_payments
Where paid_date < '11/27/2012'
Group by party_code) as TotalPaid
FROM party_payments
GROUP BY party_code


It was Giving Following Error. . .



Why are you not trying and testing out the code that was actually posted?? After I put together my OWN DLL and sample data, the query itself became trivial. NOTE: It is very close to the air code posted earlier.

SELECT t1.party_code, sum(t1.inst_amt) as total_insts, 
(Select Sum(t2.paid_amt)
From @party_payments t2
WHERE t2.paid_date < '2012-11-27' and t2.party_code = t1.party_code
Group by t2.party_code) as TotalPaid
FROM @party_payments t1
GROUP BY t1.party_code



+1 to LinksUp for providing the DDL and sample data for this question.

However, the solution above requires 3 scans of the @party_payments table (and R. Brush's CTE version requires 2 scans), which is trivial up to a few thousand rows or so, but after that can get expensive. This code gives the same result with only a single scan:

SELECT party_code
,SUM(inst_amt) AS total_insts
,SUM(CASE WHEN paid_date < '2012-11-27' THEN paid_amt ELSE 0 END) AS TotalPaid

FROM @party_payments

GROUP BY party_code



Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1497433
Posted Monday, September 23, 2013 9:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:43 AM
Points: 344, Visits: 1,322
wolfkillj (9/23/2013)


+1 to LinksUp for providing the DDL and sample data for this question.

However, the solution above requires 3 scans of the @party_payments table (and R. Brush's CTE version requires 2 scans), which is trivial up to a few thousand rows or so, but after that can get expensive. This code gives the same result with only a single scan:

SELECT party_code
,SUM(inst_amt) AS total_insts
,SUM(CASE WHEN paid_date < '2012-11-27' THEN paid_amt ELSE 0 END) AS TotalPaid

FROM @party_payments

GROUP BY party_code






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)


__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/


  Post Attachments 
Query.jpg (1 view, 114.32 KB)
Post #1497438
Posted Monday, September 23, 2013 10:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:21 PM
Points: 1,210, Visits: 2,512
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
Blog: SQLSouth
Twitter: @SQLSouth


  Post Attachments 
Subselect.sqlplan (1 view, 42.33 KB)
Post #1497460
Posted Monday, September 23, 2013 10:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:21 PM
Points: 1,210, Visits: 2,512
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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1497462
Posted Monday, September 23, 2013 10:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:43 AM
Points: 344, Visits: 1,322
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/
Post #1497465
Posted Monday, September 23, 2013 10:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:21 PM
Points: 1,210, Visits: 2,512
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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1497466
Posted Monday, September 23, 2013 10:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:43 AM
Points: 344, Visits: 1,322
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/
Post #1497468
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse