--
-- Benchmark for Tony Andrews: Scalar Subquery
-- PerformanceDBA 01 Dec 2010
--

SET STATISTICS IO ON
SET STATISTICS TIME ON

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

----------
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.
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.
Total writes for this command: 0
 
Execution Time 0.
Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.
Table: REF_Customer (c)    scan count 1,   logical reads: (regular=4     apf=0 total=4),     physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: CustomerTransaction scan count 100, logical reads: (regular=19169 apf=0 total=19169), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: CustomerTransaction scan count 100, logical reads: (regular=19169 apf=0 total=19169), 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: 1083 ms.