Efficient SQL Statement for Large Tables
I'm hoping to find an efficient way to query 2 very large SQL tables (each with 5-6 million rows).
One table holds the prior month's data (OldTable). The 2nd table holds current month's data (NewTable). Both tables have the same structure and there are 120 columns in each table.
I have to create a report showing the top 10 rows from the NewTable of each column that has changed compared with OldTable.
This means 120 Select statements using an Inner Join on 2 tables with millions of rows in each table. For each column there is a different Select statement with it's corresponding Where clause.
Needless to say it's taking days (yes, days) to get through this.
This report is something that we have to have.
Here is the code for just one of the 120 Select statements:
-- ChangedMaxDiscount values
SELECT TOP 10 'ChangedMaxDiscount', cur.LinkID, cur.MaxDiscount, old.MaxDiscount
FROM outLink as cur
INNER JOIN OldLink as old
ON cur.LinkName = old.LinkName
AND cur.CodeFrom = old.CodeFrom
AND cur.EffectiveDate = old.EffectiveDate
WHERE (cur.MaxDiscount is NULL AND old.MaxDiscount is NOT NULL)
OR (cur.MaxDiscount is NOT NULL AND old.MaxDiscount is NULL)
OR (cur.MaxDiscount <> old.MaxDiscount)
I believe there has to be a more efficient way for me to obtain this information. Any suggestions would be greatly appreciated.