SHARE
TWEET

Untitled

a guest Oct 7th, 2015 340 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT @@VERSION
  2. --Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
  3. --      Jun  9 2015 12:06:16
  4. --      Copyright (c) Microsoft Corporation
  5. --      Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
  6.  
  7. /*************************************************************************************
  8. * SETUP
  9. *************************************************************************************/
  10.  
  11. -- Create a million rows of previously existing "customers"
  12. -- We use a string representation because the customer identifer provided
  13. -- by the client may not be a number (or may be NULL in some cases).
  14. SELECT TOP 1000000 id = IDENTITY(INT, 1, 1),
  15.         CONVERT(VARCHAR(50),ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS cust_nbr
  16. INTO #existingCustomers
  17. FROM master..spt_values v1
  18. CROSS JOIN master..spt_values v2
  19. GO
  20.  
  21. -- Create 10K rows of customers who might be new, based on the string
  22. -- customer identifier provided by the client.
  23. SELECT TOP 10000 CONVERT(VARCHAR(100), 1000 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS cust_nbr
  24. INTO #potentialNewCustomers
  25. FROM master..spt_values v1
  26. CROSS JOIN master..spt_values v2
  27. GO
  28.  
  29. /*************************************************************************************
  30. * TEST QUERY
  31. *************************************************************************************/
  32.  
  33. -- Prune any existing customers from the set of potential new customers
  34. -- This query is much slower than expected in SQL 2014
  35. SELECT *
  36. FROM #potentialNewCustomers -- 10K rows
  37. WHERE cust_nbr NOT IN (
  38.         SELECT cust_nbr
  39.         FROM #existingCustomers -- 1MM rows
  40. )
  41. --OPTION (QUERYTRACEON 9481) -- Use SQL 2012 CE
  42. -- 2012 CE
  43.         -- CPU time = 3323 ms,  elapsed time = 861 ms.
  44.         -- Table '#existingCustomers'. Scan count 13, logical reads 31685
  45.         -- Table '#potentialNewCustomers'. Scan count 5, logical reads 25
  46. -- 2014 CE
  47.         -- CPU time = 656514 ms,  elapsed time = 170265 ms.
  48.         -- Table '#existingCustomers'. Scan count 12, logical reads 22004958
  49.         -- Table '#potentialNewCustomers'. Scan count 5, logical reads 25
  50. GO
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