Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT @@VERSION
- --Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
- -- Jun 9 2015 12:06:16
- -- Copyright (c) Microsoft Corporation
- -- Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
- /*************************************************************************************
- * SETUP
- *************************************************************************************/
- -- Create a million rows of previously existing "customers"
- -- We use a string representation because the customer identifer provided
- -- by the client may not be a number (or may be NULL in some cases).
- SELECT TOP 1000000 id = IDENTITY(INT, 1, 1),
- CONVERT(VARCHAR(50),ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS cust_nbr
- INTO #existingCustomers
- FROM master..spt_values v1
- CROSS JOIN master..spt_values v2
- GO
- -- Create 10K rows of customers who might be new, based on the string
- -- customer identifier provided by the client.
- SELECT TOP 10000 CONVERT(VARCHAR(100), 1000 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS cust_nbr
- INTO #potentialNewCustomers
- FROM master..spt_values v1
- CROSS JOIN master..spt_values v2
- GO
- /*************************************************************************************
- * TEST QUERY
- *************************************************************************************/
- -- Prune any existing customers from the set of potential new customers
- -- This query is much slower than expected in SQL 2014
- SELECT *
- FROM #potentialNewCustomers -- 10K rows
- WHERE cust_nbr NOT IN (
- SELECT cust_nbr
- FROM #existingCustomers -- 1MM rows
- )
- --OPTION (QUERYTRACEON 9481) -- Use SQL 2012 CE
- -- 2012 CE
- -- CPU time = 3323 ms, elapsed time = 861 ms.
- -- Table '#existingCustomers'. Scan count 13, logical reads 31685
- -- Table '#potentialNewCustomers'. Scan count 5, logical reads 25
- -- 2014 CE
- -- CPU time = 656514 ms, elapsed time = 170265 ms.
- -- Table '#existingCustomers'. Scan count 12, logical reads 22004958
- -- Table '#potentialNewCustomers'. Scan count 5, logical reads 25
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement