--
-- Benchmark for Tony Andrews: Inline 3 Improved COUNT()
-- PerformanceDBA 01 Dec 2010
--

SET STATISTICS IO ON
SET STATISTICS TIME ON
SET SHOWPLAN ON
SET NOEXEC ON

SELECT  a.ShortName,
        a.NumCredit,
        b.NumDebit 
    FROM ( 
        SELECT ShortName,
                  NumCredit = ( 
            SELECT COUNT (ct.CustomerId)
                FROM  CustomerTransaction ct
                WHERE c.CustomerId = ct.CustomerId
                AND   TransactionCode ="C"
                )
            FROM  REF_Customer c
            ) a, 
        ( 
        SELECT  ShortName,
                  NumDebit  = ( 
            SELECT COUNT (ct.CustomerId)
                FROM  CustomerTransaction ct
                WHERE c.CustomerId = ct.CustomerId
                AND   TransactionCode ="D"
                )
            FROM  REF_Customer c
            ) b
    WHERE a.ShortName = b.ShortName

        
----------
QUERY PLAN FOR STATEMENT 5 (at line 6).
----------


    STEP 1
        The type of query is SELECT.

	8 operator(s) under root

       |ROOT:EMIT Operator (VA = 8)
       |
       |   |SQFILTER Operator (VA = 7) has 3 children.
       |   |
       |   |   |NESTED LOOP JOIN Operator (VA = 2) (Join Type: Inner Join)
       |   |   |
       |   |   |   |SCAN Operator (VA = 0)
       |   |   |   |  FROM TABLE
       |   |   |   |  REF_Customer
       |   |   |   |  c
       |   |   |   |  Table Scan.
       |   |   |   |  Forward Scan.
       |   |   |   |  Positioning at start of table.
       |   |   |   |  Using I/O Size 2 Kbytes for data pages.
       |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
       |   |   |
       |   |   |   |SCAN Operator (VA = 1)
       |   |   |   |  FROM TABLE
       |   |   |   |  REF_Customer
       |   |   |   |  c
       |   |   |   |  Using Clustered Index.
       |   |   |   |  Index : UC_ShortName
       |   |   |   |  Forward Scan.
       |   |   |   |  Positioning by key.
       |   |   |   |  Keys are:
       |   |   |   |    ShortName ASC
       |   |   |   |  Using I/O Size 2 Kbytes for data pages.
       |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
       |   |
       |   |  Run subquery 1 (at nesting level 1).
       |   |
       |   |  QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 10).
       |   |
       |   |   Correlated Subquery.
       |   |   Subquery under an EXPRESSION predicate.
       |   |
       |   |   |SCALAR AGGREGATE Operator (VA = 4)
       |   |   |  Evaluate Ungrouped COUNT AGGREGATE.
       |   |   |
       |   |   |   |SCAN Operator (VA = 3)
       |   |   |   |  FROM TABLE
       |   |   |   |  CustomerTransaction
       |   |   |   |  ct
       |   |   |   |  Using Clustered Index.
       |   |   |   |  Index : UC_PK
       |   |   |   |  Forward Scan.
       |   |   |   |  Positioning by key.
       |   |   |   |  Keys are:
       |   |   |   |    CustomerId ASC
       |   |   |   |  Using I/O Size 8 Kbytes for data pages.
       |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
       |   |
       |   |  END OF QUERY PLAN FOR SUBQUERY 1.
       |   |
       |   |  Run subquery 2 (at nesting level 0).
       |   |
       |   |  QUERY PLAN FOR SUBQUERY 2 (at nesting level 0 and at line 18).
       |   |
       |   |   Correlated Subquery.
       |   |   Subquery under an EXPRESSION predicate.
       |   |
       |   |   |SCALAR AGGREGATE Operator (VA = 6)
       |   |   |  Evaluate Ungrouped COUNT AGGREGATE.
       |   |   |
       |   |   |   |SCAN Operator (VA = 5)
       |   |   |   |  FROM TABLE
       |   |   |   |  CustomerTransaction
       |   |   |   |  ct
       |   |   |   |  Using Clustered Index.
       |   |   |   |  Index : UC_PK
       |   |   |   |  Forward Scan.
       |   |   |   |  Positioning by key.
       |   |   |   |  Keys are:
       |   |   |   |    CustomerId ASC
       |   |   |   |  Using I/O Size 8 Kbytes for data pages.
       |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
       |   |
       |   |  END OF QUERY PLAN FOR SUBQUERY 2.