question

Peter001 avatar image
Peter001 asked

Performance hit while Comparing Rows in absence of key

Hello, May anyone please help me on this. database: SQL SERVER 2008 18 Million records i have two table and i need to check missing rows between two table, as i don't have any keycolumn i concatenated all the column and called it as oldrow and newrow But my problem is this query tooks me more than 6 hours and still executing even after indexing done. Can anyone please suggest me some other solution using BINARY_CHECKSUM or HASHBYTES **Query I tried:** SELECT , OLD.Resistor , OLD.ResistorID , OLD.Capacitor , OLD.CapacitorID , OLD.Years ,CASE WHEN New.NewRow IS NULL THEN 'Record Deleted' ELSE 'No Change' END AS FixMessage FROM #oldfinal Old LEFT OUTER JOIN #newfinal New ON Old.OldRow = New.NewRow UNION SELECT , NEW.Resistor , NEW.ResistorID , NEW.Capacitor , NEW.CapacitorID , NEW.Years , 'Record Added' AS FixMessage FROM #newfinal New LEFT OUTER JOIN #oldfinal Old ON New.NewRow = Old.OldRow WHERE Old.OldRow IS NULL None of the column is unique, all contain duplicate value, i have added index but in vain will it be possible to include BINARY_CHECKSUM or HASHBYTES . Please share your expertise. Thanks,
sql-server-2008sql-server-2005t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
nidheesh.r.pillai avatar image
nidheesh.r.pillai answered
(Select [column_list] from Table1 Except Select [column_list] from Table2) UNION (Select [column_list] from Table2 Except Select [column_list] from Table1) The above should help you and give you any deleted or added rows in both of the tables.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.