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

SET STATISTICS IO ON
SET STATISTICS TIME ON

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

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

Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
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: REF_Customer (c)         scan count 1000, logical reads: (regular=2007  apf=0 total=2007),  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: 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
Total writes for this command: 0
 
Execution Time 11.
Adaptive Server cpu time: 1100 ms.  Adaptive Server elapsed time: 1183 ms.