• 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