Guest User

ED Testing KF indexes

a guest
Dec 2nd, 2014
279
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

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×