Hi.

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.

TIA.

Rita

Are the index the same on both tables?
are you comparing contents?


If tableA and tableB have unique indexes on them:
select count(*) from tableA
select count(*) from tableB

If they don't have unique indexes on them, then do
select count(*) from (select distinct * from tableA) as a
select count(*) from (select distinct * from tableB) as b

Both tables have the same indexes and I'm checking the content of the rows.

Select Count(*) doesn't help me as I need to know if individual columns have changed since last month.

Thanks.

Rita

I supose you do have an index on LinkName, CodeFrom, EffectiveDate, don't you?

or, even better, a clustered index