SHARE
TWEET

ED Testing KF indexes

a guest Dec 2nd, 2014 217 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT *
  2.     INTO date_CI_TransactionHistory
  3. FROM Production.TransactionHistory;
  4.  
  5. ALTER TABLE date_CI_TransactionHistory ADD CONSTRAINT
  6.     PK_date_CI_TransactionHistory PRIMARY KEY /*NONCLUSTERED*/ (TransactionDate, TransactionID);
  7. --CREATE CLUSTERED INDEX IX_date_CI_TransactionHistory_TransactionDate
  8. --    ON date_CI_TransactionHistory(TransactionDate, ProductID);
  9. CREATE NONCLUSTERED INDEX IX_date_CI_TransactionHistory_ProductID
  10.     ON date_CI_TransactionHistory(ProductID);
  11. CREATE NONCLUSTERED INDEX IX_date_CI_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
  12.     ON date_CI_TransactionHistory(ReferenceOrderID, ReferenceOrderLineID);
  13. GO
  14.  
  15. --DROP TABLE date_CI_TransactionHistory
  16.  
  17. SELECT *
  18.     INTO int_CI_TransactionHistory
  19. FROM Production.TransactionHistory;
  20.  
  21. ALTER TABLE int_CI_TransactionHistory ADD CONSTRAINT
  22.     PK_int_CI_TransactionHistory PRIMARY KEY (TransactionID, TransactionDate);
  23. CREATE NONCLUSTERED INDEX IX_int_CI_TransactionHistory_TransactionDate
  24.     ON int_CI_TransactionHistory(TransactionDate);
  25. CREATE NONCLUSTERED INDEX IX_int_CI_TransactionHistory_ProductID
  26.     ON int_CI_TransactionHistory(ProductID);
  27. CREATE NONCLUSTERED INDEX IX_int_CI_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
  28.     ON int_CI_TransactionHistory(ReferenceOrderID, ReferenceOrderLineID);
  29. GO
  30.  
  31. --DROP TABLE int_CI_TransactionHistory
  32.  
  33. EXEC sp_spaceused date_CI_TransactionHistory
  34. EXEC sp_spaceused int_CI_TransactionHistory
  35.  
  36. SET STATISTICS IO ON
  37. SET STATISTICS TIME ON
  38.  
  39. SELECT *
  40. FROM date_CI_TransactionHistory
  41. WHERE TransactionDate BETWEEN '2014-05-01' AND '2014-05-31'
  42.  
  43. /*
  44. SQL Server parse and compile time:
  45.    CPU time = 0 ms, elapsed time = 0 ms.
  46. SQL Server parse and compile time:
  47.    CPU time = 0 ms, elapsed time = 0 ms.
  48. Table 'date_CI_TransactionHistory'. Scan count 1, logical reads 93, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  49.  
  50.  SQL Server Execution Times:
  51.    CPU time = 0 ms,  elapsed time = 144 ms.
  52. */
  53.  
  54. SELECT *
  55. FROM int_CI_TransactionHistory
  56. WHERE TransactionDate BETWEEN '2014-05-01' AND '2014-05-31'
  57.  
  58.  
  59. /*
  60. SQL Server parse and compile time:
  61.    CPU time = 0 ms, elapsed time = 0 ms.
  62. SQL Server parse and compile time:
  63.    CPU time = 0 ms, elapsed time = 0 ms.
  64. Table 'int_CI_TransactionHistory'. Scan count 1, logical reads 788, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  65.  
  66.  SQL Server Execution Times:
  67.    CPU time = 16 ms,  elapsed time = 160 ms.
  68. */
  69.  
  70. SELECT *
  71. FROM date_CI_TransactionHistory
  72. WHERE TransactionDate = '2014-05-05 00:00:00.000'
  73.  
  74. /*
  75. SQL Server parse and compile time:
  76.    CPU time = 0 ms, elapsed time = 0 ms.
  77. SQL Server parse and compile time:
  78.    CPU time = 0 ms, elapsed time = 0 ms.
  79. Table 'date_CI_TransactionHistory'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  80.  
  81.  SQL Server Execution Times:
  82.    CPU time = 0 ms,  elapsed time = 75 ms.
  83.  
  84. */
  85.  
  86. SELECT *
  87. FROM int_CI_TransactionHistory
  88. WHERE TransactionDate = '2014-05-05 00:00:00.000'
  89.  
  90. /*
  91. SQL Server parse and compile time:
  92.    CPU time = 0 ms, elapsed time = 0 ms.
  93. SQL Server parse and compile time:
  94.    CPU time = 0 ms, elapsed time = 0 ms.
  95. Table 'int_CI_TransactionHistory'. Scan count 1, logical reads 788, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  96.  
  97.  SQL Server Execution Times:
  98.    CPU time = 0 ms,  elapsed time = 75 ms.
  99. */
  100.  
  101. SELECT *
  102. FROM date_CI_TransactionHistory
  103. WHERE TransactionID = 166032
  104.  
  105. /*
  106. SQL Server parse and compile time:
  107.    CPU time = 1 ms, elapsed time = 1 ms.
  108. SQL Server parse and compile time:
  109.    CPU time = 0 ms, elapsed time = 0 ms.
  110. Table 'date_CI_TransactionHistory'. Scan count 1, logical reads 314, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  111.  
  112.  SQL Server Execution Times:
  113.    CPU time = 0 ms,  elapsed time = 4 ms.
  114. */
  115.  
  116. SELECT *
  117. FROM int_CI_TransactionHistory
  118. WHERE TransactionID = 166032
  119.  
  120. /*
  121. SQL Server parse and compile time:
  122.    CPU time = 0 ms, elapsed time = 0 ms.
  123. SQL Server parse and compile time:
  124.    CPU time = 0 ms, elapsed time = 0 ms.
  125. Table 'int_CI_TransactionHistory'. Scan count 1, logical reads 788, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  126.  
  127.  SQL Server Execution Times:
  128.    CPU time = 0 ms,  elapsed time = 4 ms.
  129. */
  130.  
  131. SELECT *
  132. FROM date_CI_TransactionHistory CI_Test
  133. JOIN production.TransactionHistory Orig
  134.     ON CI_Test.TransactionID = Orig.TransactionID
  135. WHERE Orig.Quantity > 30
  136.  
  137. /*
  138. SQL Server parse and compile time:
  139.    CPU time = 0 ms, elapsed time = 0 ms.
  140. Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  141. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  142. Table 'date_CI_TransactionHistory'. Scan count 1, logical reads 793, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  143. Table 'TransactionHistory'. Scan count 1, logical reads 797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  144.  
  145.  SQL Server Execution Times:
  146.    CPU time = 47 ms,  elapsed time = 186 ms.
  147.  
  148. */
  149.  
  150. SELECT *
  151. FROM int_CI_TransactionHistory CI_Test
  152. JOIN production.TransactionHistory Orig
  153.     ON CI_Test.TransactionID = Orig.TransactionID
  154. WHERE Orig.Quantity > 30
  155.  
  156. /*
  157. SQL Server parse and compile time:
  158.    CPU time = 0 ms, elapsed time = 0 ms.
  159. Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  160. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  161. Table 'int_CI_TransactionHistory'. Scan count 1, logical reads 788, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  162. Table 'TransactionHistory'. Scan count 1, logical reads 797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  163.  
  164.  SQL Server Execution Times:
  165.    CPU time = 47 ms,  elapsed time = 186 ms.
  166. */
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top