1

I am trying to locate fastest way to find difference between two tables. The first table is populated from text files daily, the second table is maintained in our database. We need to add data that is new or has changed from the text files. The comparison is done on the primary key.

create table dbo.CustomerTransaction
(
    CustomerTransactionId int primary key,
    CustomerName varchar(50),
    ProductName varchar(50),
    QuantityBought int
)

So row values Table 1: (1,'Bob','Table',8) is the Same as Table 2: (1,'Bob','Table',8)

These are different (1,'Bob','Table',8) , (1,'Bob','Chair',8) , different on the primary key.

I am seeing a lot of articles discussing methods, with Left Join, Except, tablediff, Visual Studio DataComparison, Union group.

However, no one talks about performance/speed. Which is the fastest internal algorithm way? I am guessing it is the TableDiff Utility, which is a SQL tool designed specifically for this purpose.

We do not have access to CDC in legacy text file system, so we are extracting all new data into SQL Server daily, and comparing to previous data. We are finding incremental load values, and placing into Kimball Data Warehouse.

Neither table is actively being used at the time we are doing the comparison.

How to find out the content differences between 2 SQL tables and produce sync SQL https://stackoverflow.com/questions/4602083/sql-compare-data-from-two-tables

Improve this question
4
  • 1
    Here is an answer discussing LEFT JOIN vs NOT EXISTS, and then here is one comparing NOT EXISTS to NOT IN. EXCEPT looks at all columns, and since you are basing it off the PK only you wouldn't want to use it. – scsimon Oct 15 '18 at 18:18
  • 2
    It all depends. We can't tell you with any certainty what will be faster; you'll have to test. For a specific use case I explore the winner(s) here, but there is no guarantee that will be fastest for you. – Aaron Bertrand Oct 15 '18 at 18:31
  • I remember seeing the link, I will need to compare with Tablediff – user162241 Oct 15 '18 at 19:30
  • If anyone comes across, here is the method that worked for me best comparing to all the others: weblogs.sqlteam.com/jeffs/2004/11/10/2737 – Oleksandr Mar 19 at 1:25
1

There is no firm answer. David Lozinski conducted a study below, and found different methods succeed depending on row count, statistics, cardinality.

Fastest way to insert new records where one doesn’t already exist

I had an opportunity to test the tablediff utility, for some reason it was performing lot slower (2-3x slower) compared to T-SQL methods below. However it is good for conducting cross-server table differences (originally used in Replication method).

enter image description here

Improve this answer

Your Answer

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