Current advice for the most efficient way to compare two large result/row sets seems to be to use the EXCEPT operator. This self contained SQL script below gets very inefficient as row sizes increase (change @last values). I have tried to find unique entries in a combined table but with no improvement.

DECLARE @first AS INT, @step AS INT, @last AS INT; 

-- This script is comparing two record sets using EXCEPT
-- I want to find additions from OLD to NEW
-- As number of rows increase performance gets terrible
-- I don't have to use two tables. I could use one combined table but I want the same result as quickly as possible

-- Compare 100 to 110 rows - 0 seconds
-- Compare 1000 to 1010 rows - 1 seconds
-- Compare 10000 to 10010 rows - 16 seconds
-- Compare 100000 to 100010 rows - ABORT after 8 minutes (tables are populated in 18 seconds)

DECLARE @temptableOLD TABLE ([Result1] int);
SET @step = 1;  SET @first = 1; SET @last = 100000
WHILE(@first <= @last) BEGIN INSERT INTO @temptableOLD VALUES(@first) SET @first += @step END

DECLARE @temptableNEW TABLE ([Result1] int);
SET @step = 1;  SET @first = 1; SET @last = 100010
WHILE(@first <= @last) BEGIN INSERT INTO @temptableNEW VALUES(@first) SET @first += @step END

select * from @temptableNEW
select * from @temptableOLD

EXCEPT implies a DISTINCT operation.

I would use NOT EXISTS if this is not actually required.

However the problem you are having is likely that you are getting a nested loops on an unindexed table due to the poor cardinality estimates associated with table variables.

select * from @temptableNEW
select * from @temptableOLD

Will be able to take account that the tables have 100K rows each and give a different plan.

In SQL Server 2012 you can only add indexes to table variables via constraints. If the values are unique you could use


to add an index. If done on both tables the plan (after the recompile hint is added) will likely use a merge join instead. Without any indexes I would expect a hash join.

|improve this answer|||||
  • Thank you Martin. This is the answer. OPTION (RECOMPILE) helped (100,000 in 5 minutes), but UNIQUE CLUSTERED on both tables made the big improvement (100,000 in 7 seconds!!!). I only created these tables to demonstrate a real life problem where I have no control over the table indexing on two different SQL servers, but I will manage it through such table variables. – Will Healey Dec 27 '15 at 8:48
  • 4
    @WillHealey #temp tables have a lot of advantages over table variables (statistics, parallelism, more flexible indexing) so if you aren't using this in a context where you are restricted to Table variables you could try those too. – Martin Smith Dec 27 '15 at 8:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.