-- -- Benchmark for Tony Andrews: Scalar Subquery SUM() -- PerformanceDBA 01 Dec 2010 -- SET SHOWPLAN ON SET NOEXEC ON SELECT ShortName, NumCredit = ( SELECT SUM (Amount) FROM CustomerTransaction WHERE CustomerId = c.CustomerId AND TransactionCode ="C" ), NumDebit = ( SELECT SUM (Amount) FROM CustomerTransaction WHERE CustomerId = c.CustomerId AND TransactionCode ="D" ) FROM REF_Customer c ---------- QUERY PLAN FOR STATEMENT 5 (at line 6). ---------- STEP 1 The type of query is SELECT. 6 operator(s) under root |ROOT:EMIT Operator (VA = 6) | | |SQFILTER Operator (VA = 5) has 3 children. | | | | |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. | | | | Run subquery 1 (at nesting level 1). | | | | QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 7). | | | | Correlated Subquery. | | Subquery under an EXPRESSION predicate. | | | | |SCALAR AGGREGATE Operator (VA = 2) | | | Evaluate Ungrouped SUM OR AVERAGE AGGREGATE. | | | | | | |SCAN Operator (VA = 1) | | | | FROM TABLE | | | | CustomerTransaction | | | | 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 1). | | | | QUERY PLAN FOR SUBQUERY 2 (at nesting level 1 and at line 12). | | | | Correlated Subquery. | | Subquery under an EXPRESSION predicate. | | | | |SCALAR AGGREGATE Operator (VA = 4) | | | Evaluate Ungrouped SUM OR AVERAGE AGGREGATE. | | | | | | |SCAN Operator (VA = 3) | | | | FROM TABLE | | | | CustomerTransaction | | | | 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.