--
-- Benchmark for Tony Andrews:  Duplicated column allowing Flat Query
-- PerformanceDBA 08 Dec 2010
--

SET STATISTICS IO, TIME ON
SET SHOWPLAN ON

-- Report all Products with Current State of 3
-- Dupe Flat 

SELECT  p.ProductId, 
        p.Description, 
        ps.StartDateTime --,
        --ps.EndDateTime 
    FROM  Product           p, 
          ProductStatusDupe ps 
    WHERE p.ProductId = ps.ProductId 
    AND   ps.StatusCode = 3
    AND   ( GETDATE() BETWEEN ps.StartDateTime 
        AND   ps.EndDateTime
        )
SELECT @@ROWCOUNT

----------------
-- Query Plan --
----------------

QUERY PLAN FOR STATEMENT 3 (at line 7).


    STEP 1
        The type of query is SELECT.

	4 operator(s) under root

       |ROOT:EMIT Operator (VA = 4)
       |
       |   |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
       |   |
       |   |   |RESTRICT Operator (VA = 1)(3)(0)(0)(0)(0)
       |   |   |
       |   |   |   |SCAN Operator (VA = 0)
       |   |   |   |  FROM TABLE
       |   |   |   |  ProductStatusDupe
       |   |   |   |  ps
       |   |   |   |  Table Scan.
       |   |   |   |  Forward Scan.
       |   |   |   |  Positioning at start of table.
       |   |   |   |  Using I/O Size 8 Kbytes for data pages.
       |   |   |   |  With MRU Buffer Replacement Strategy for data pages.
       |   |
       |   |   |SCAN Operator (VA = 2)
       |   |   |  FROM TABLE
       |   |   |  Product
       |   |   |  p
       |   |   |  Using Clustered Index.
       |   |   |  Index : UC_ProductId
       |   |   |  Forward Scan.
       |   |   |  Positioning by key.
       |   |   |  Keys are:
       |   |   |    ProductId ASC
       |   |   |  Using I/O Size 2 Kbytes for data pages.
       |   |   |  With LRU Buffer Replacement Strategy for data pages.

---------------------
-- Execution Stats --
---------------------

Execution Time 0.
Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.
Table: ProductStatusDupe (ps) scan count 1,     logical reads: (regular=25000 apf=0 total=25000), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Product (p)            scan count 20000, logical reads: (regular=60000 apf=0 total=60000), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0
 
Execution Time 5.
Adaptive Server cpu time: 500 ms.  Adaptive Server elapsed time: 1023 ms.
Total writes for this command: 0