January 17, 2012 at 10:38 pm
that's all right.
ChrisM@home (1/15/2012)
Sample data for testing:
DROP TABLE Table_1
CREATE TABLE Table_1(
RowID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,
custid INT,
store VARCHAR(9) NOT NULL,
amount INT)
INSERT INTO Table_1 (custid, store, amount) -- 2 million rows
SELECT [custid], [store], [Amount]
FROM (SELECT TOP 1000 [custid] = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.columns a, sys.columns b) customers
CROSS JOIN (SELECT [store] = 'store' + CAST(rn AS VARCHAR(4)) FROM (
SELECT TOP 1000 rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.columns a, sys.columns b)
d) stores
CROSS JOIN (SELECT [Amount] = 1000 UNION ALL SELECT 2000) Amounts
CREATE NONCLUSTERED INDEX [IX_Store] ON Table_1 (Store)
DROP TABLE Table_2
CREATE TABLE Table_2 (
RowID INT IDENTITY (1,1),
store VARCHAR(9) NOT NULL,
product VARCHAR(8),
value INT)
INSERT INTO Table_2 (s.store, p.product, v.value) -- 7920040 rows
SELECT store, product, value
FROM (SELECT [store] = 'store' + CAST(rn AS VARCHAR(4)) FROM (
SELECT TOP 1000 rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.columns a, sys.columns b)
d) s
CROSS JOIN (SELECT [product] = 'prod' + CAST(rn AS VARCHAR(4)) FROM (
SELECT TOP 2000 rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.columns a, sys.columns b)
d) p
CROSS JOIN (SELECT [Value] = 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) v
WHERE NOT (product != 'prod6' AND store IN ('store126', 'store185', 'store284', 'store299', 'store359', 'store667', 'store682', 'store793', 'store947', 'store957'))
/*
If I create table_2 with "RowID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, ", then the following error occurs:
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
The error doesn't occur if there are only 2 values for [value] i.e. table half the size
*/
CREATE UNIQUE CLUSTERED INDEX [CX_RowID] ON Table_2 (RowID)
CREATE NONCLUSTERED INDEX [IX_Store_product] ON Table_2 (Store, product)
Testing:
DECLARE @custid INT, @store VARCHAR(9), @amount INT
SET STATISTICS IO,TIME ON
DBCC DROPCLEANBUFFERS
PRINT 'Query 1 -----------------------------------------------------------------'
--DECLARE @custid INT, @store VARCHAR(9), @amount INT
select @custid = custid, @store = store, @amount = Amount
from Table_1 where store not in (select store from table_2 where product != 'prod6')
PRINT 'Query 1 ================================================================='
DBCC DROPCLEANBUFFERS
PRINT 'Query 2 -----------------------------------------------------------------'
--DECLARE @custid INT, @store VARCHAR(9), @amount INT
;WITH Table_2_Stores AS
(
SELECT store = MIN(T.store)
FROM dbo.Table_2 T
WHERE product != 'prod6'
UNION ALL
SELECT R.store
FROM (
SELECT T.store,
rn = ROW_NUMBER() OVER (ORDER BY T.store)
FROM dbo.Table_2 T
JOIN Table_2_Stores R
ON R.store < T.store
WHERE T.product != 'prod6'
) R
WHERE R.rn = 1
)
SELECT @custid = custid, @store = store, @amount = Amount
FROM Table_1 t1
WHERE store NOT IN (SELECT store FROM Table_2_Stores)
OPTION (MAXRECURSION 0);
PRINT 'Query 2 ================================================================='
Results:
Query 1 -----------------------------------------------------------------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Table_2'. Scan count 3, logical reads 31014, physical reads 78, read-ahead reads 7124, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table_1'. Scan count 3, logical reads 8245, physical reads 43, read-ahead reads 8176, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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.
SQL Server Execution Times:
CPU time = 7456 ms, elapsed time = 6335 ms.
Query 2 -----------------------------------------------------------------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 6, logical reads 17823, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table_2'. Scan count 2973, logical reads 8922, physical reads 227, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table_1'. Scan count 3, logical reads 8245, physical reads 43, read-ahead reads 8176, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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.
SQL Server Execution Times:
CPU time = 2247 ms, elapsed time = 3330 ms.
Subjectively, it's different - Q2 runs in about a second, Q1 in about 8 seconds. <-- Edit: corrected
Q2 is an idea nicked from here, another of Paul White's excellent queries.
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply