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

SET STATISTICS IO, TIME ON
SET SHOWPLAN ON

-- Report all Products with Current State of 3
-- Bit flat

SELECT  p.ProductId,
        p.Description, 
        ps.DateTime,
        --ps.EndDateTime,
        ps.StatusCode 
    FROM  Product          p,
          ProductStatusBit ps
    WHERE p.ProductId = ps.ProductId  -- Join
    AND   StatusCode  = 3             -- Request
    AND   IsCurrent   = 1
SELECT @@ROWCOUNT

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

QUERY PLAN FOR STATEMENT 3 (at line 7).


    STEP 1
        The type of query is SELECT.

	3 operator(s) under root

       |ROOT:EMIT Operator (VA = 3)
       |
       |   |NESTED LOOP JOIN Operator (VA = 2) (Join Type: Inner Join)
       |   |
       |   |   |SCAN Operator (VA = 0)
       |   |   |  FROM TABLE
       |   |   |  ProductStatusBit
       |   |   |  ps
       |   |   |  Table Scan.
       |   |   |  Forward Scan.
       |   |   |  Positioning at start of table.
       |   |   |  Using I/O Size 8 Kbytes for data pages.
       |   |   |  With LRU Buffer Replacement Strategy for data pages.
       |   |
       |   |   |SCAN Operator (VA = 1)
       |   |   |  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: ProductStatusBit (ps) scan count 1,     logical reads: (regular=17858 apf=0 total=17858), 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 1.
Adaptive Server cpu time: 100 ms.  Adaptive Server elapsed time: 750 ms.
Total writes for this command: 0