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

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

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

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

    STEP 1
        The type of query is SELECT.

	13 operator(s) under root

       |ROOT:EMIT Operator (VA = 13)
       |
       |   |SEQUENCER Operator (VA = 12) has 2 children.
       |   |
       |   |   |STORE Operator (VA = 5)
       |   |   |  Worktable2 created, in allpages locking mode, for REFORMATTING.
       |   |   |  Creating clustered index.
       |   |   |
       |   |   |   |INSERT Operator (VA = 4)
       |   |   |   |  The update mode is direct.
       |   |   |   |
       |   |   |   |   |HASH VECTOR AGGREGATE Operator (VA = 3)
       |   |   |   |   |  GROUP BY
       |   |   |   |   |  Evaluate Grouped SUM OR AVERAGE AGGREGATE.
       |   |   |   |   | Using Worktable1 for internal storage.
       |   |   |   |   |  Key Count: 1
       |   |   |   |   |
       |   |   |   |   |   |NESTED LOOP JOIN Operator (VA = 2) (Join Type: Inner Join)
       |   |   |   |   |   |
       |   |   |   |   |   |   |SCAN Operator (VA = 0)
       |   |   |   |   |   |   |  FROM TABLE
       |   |   |   |   |   |   |  REF_Customer
       |   |   |   |   |   |   |  c
       |   |   |   |   |   |   |  Index : U_CustomerId
       |   |   |   |   |   |   |  Forward Scan.
       |   |   |   |   |   |   |  Positioning at index start.
       |   |   |   |   |   |   |  Using I/O Size 2 Kbytes for index leaf pages.
       |   |   |   |   |   |   |  With LRU Buffer Replacement Strategy for index leaf pages.
       |   |   |   |   |   |   |  Using I/O Size 2 Kbytes for data pages.
       |   |   |   |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
       |   |   |   |   |   |
       |   |   |   |   |   |   |SCAN Operator (VA = 1)
       |   |   |   |   |   |   |  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.
       |   |   |   |
       |   |   |   |  TO TABLE
       |   |   |   |  Worktable2.
       |   |
       |   |   |NESTED LOOP JOIN Operator (VA = 11) (Join Type: Inner Join)
       |   |   |
       |   |   |   |HASH VECTOR AGGREGATE Operator (VA = 9)
       |   |   |   |  GROUP BY
       |   |   |   |  Evaluate Grouped SUM OR AVERAGE AGGREGATE.
       |   |   |   | Using Worktable3 for internal storage.
       |   |   |   |  Key Count: 1
       |   |   |   |
       |   |   |   |   |NESTED LOOP JOIN Operator (VA = 8) (Join Type: Inner Join)
       |   |   |   |   |
       |   |   |   |   |   |SCAN Operator (VA = 6)
       |   |   |   |   |   |  FROM TABLE
       |   |   |   |   |   |  REF_Customer
       |   |   |   |   |   |  c
       |   |   |   |   |   |  Index : U_CustomerId
       |   |   |   |   |   |  Forward Scan.
       |   |   |   |   |   |  Positioning at index start.
       |   |   |   |   |   |  Using I/O Size 2 Kbytes for index leaf pages.
       |   |   |   |   |   |  With LRU Buffer Replacement Strategy for index leaf pages.
       |   |   |   |   |   |  Using I/O Size 2 Kbytes for data pages.
       |   |   |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
       |   |   |   |   |
       |   |   |   |   |   |SCAN Operator (VA = 7)
       |   |   |   |   |   |  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.
       |   |   |
       |   |   |   |SCAN Operator (VA = 10)
       |   |   |   |  FROM TABLE
       |   |   |   |  Worktable2.
       |   |   |   |  Using Clustered Index.
       |   |   |   |  Forward Scan.
       |   |   |   |  Positioning by key.
       |   |   |   |  Using I/O Size 2 Kbytes for data pages.
       |   |   |   |  With LRU Buffer Replacement Strategy for data pages.

----------
Statistics
----------

Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Total writes for this command: 0
 
Execution Time 0.
Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.
Total writes for this command: 0
 
Execution Time 0.
Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.
Table: Worktable2                scan count 1,   logical reads: (regular=13    apf=0 total=13),    physical reads: (regular=3 apf=0 total=3), apf IOs used=0
Table: Worktable2                scan count 1,   logical reads: (regular=107   apf=0 total=107),   physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1                scan count 1,   logical reads: (regular=5     apf=0 total=5),     physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: REF_Customer (c)          scan count 1,   logical reads: (regular=7     apf=0 total=7),     physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: CustomerTransaction (ct)  scan count 100, logical reads: (regular=19169 apf=0 total=19169), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable3                scan count 1,   logical reads: (regular=5     apf=0 total=5),     physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: REF_Customer (c)          scan count 1,   logical reads: (regular=7     apf=0 total=7),     physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: CustomerTransaction (ct)  scan count 100, logical reads: (regular=19169 apf=0 total=19169), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable2                scan count 100, logical reads: (regular=321   apf=0 total=321),   physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 8
 
Execution Time 30.
Adaptive Server cpu time: 3000 ms.  Adaptive Server elapsed time: 3013 ms.