--
-- Benchmark for Tony Andrews: Inline View COUNT
-- PerformanceDBA 04 Dec 2010
--

SET STATISTICS IO ON
SET STATISTICS TIME ON

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

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

Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Table: Worktable1                 scan count 1,    logical reads: (regular=16    apf=0 total=16),    physical reads: (regular=6 apf=0 total=6), apf IOs used=0
Table: Worktable1                 scan count 1,    logical reads: (regular=1013  apf=0 total=1013),  physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: REF_Customer (c)           scan count 1,    logical reads: (regular=8     apf=0 total=8),     physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: CustomerTransaction_2 (ct) scan count 1000, logical reads: (regular=24104 apf=0 total=24104), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: REF_Customer (c)           scan count 1,    logical reads: (regular=8     apf=0 total=8),     physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: CustomerTransaction (ct)   scan count 1000, logical reads: (regular=24104 apf=0 total=24104), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1                 scan count 1000, logical reads: (regular=3033  apf=0 total=3033),  physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 14

Execution Time 15.
Adaptive Server cpu time: 1500 ms.  Adaptive Server elapsed time: 1553 ms.