Compare over 90 million rows of 2 Tables

Question

Hi,

i want compare 2 table contents for each row and column,

question is how can I do this, with function Except is it too slow...

Any ideas ?

Answer 1

I will try to give the answer self:

first i would run a job that concatenate all columns into one string column with special delimiter:

e.g. "col1value" + "$$--$$--$$" + "col2value" .... into colOverAll ( column)

and wrote it into the "colOverAll" column, at this point I have ONE String value to
compare the whole row with another row,
interesting question here is, how fast is the compare function ....?

Answer 2

The easier (but not more precise) is to calculate a checksum, see https://learn.microsoft.com/en-us/sql/t-sql/functions/checksum-transact-sql?view=sql-server-ver15

Answer 3

I would do:

SELECT ...
FROM   A
FULL JOIN B ON A.keycol = B.keycol
WHERE NOT EXISTS (SELECT A.* INTERSECT SELECT B.*)

Although I don't how much faster than the EXCEPT it will be. I don't think building the string will that quick either, though. It is not really that comparing all the values for a single row that takes the time. It is the number of rows. But having a key that joins them should help.

Answer 4

Hi @hamsi hamsi ，

If you can compare two tables using LEFT JOIN:

SELECT *  
FROM TESTDB.dbo.FirstComTable F  
LEFT JOIN TESTDB2.dbo.FirstComTable S  
ON F.ID =S.ID  

The LEFT JOIN T-SQL keyword is used to retrieve data from two tables, by returning all records from the left table and only the matched records from the right table and null values from the right table when there is no matching between the two tables.

Or using UNION ALL:
To return the difference between two tables, you can list the columns to compare in the SELECT statement and use these columns in the ORDER BY clause

Select * from (   
Select Id_pk, col1, col2...,coln from table1, ‘Old_table’   
Union all   
Select Id_pk, col1, col2...,coln from table2, 'New_tbale'   
) cmpr order by Id_pk;  

Answer 5

Hi guys,

thanks for all replies.

My main question is target to the performance, I know it exists a lot of SQL possible queries to

detect differences, main question here is, which query had the best Performance ?

Otherwise I can take a part of datas e.g. 2 Million and can stop the time to detect which query had the best Performance....