Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 5th, 2004, 03:58 PM
Authorized User
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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.


Old April 5th, 2004, 05:03 PM
Friend of Wrox
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc

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

Old April 5th, 2004, 05:13 PM
Authorized User
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts

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.


Old April 19th, 2004, 11:03 AM
Authorized User
Join Date: Jun 2003
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts

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

or, even better, a clustered index

Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract 8 Tables into 1 Large Table hewstone999 Access VBA 1 March 4th, 2008 09:19 AM
Fast and Efficient Async SQL Process rodmcleay ASP.NET 1.x and 2.0 Application Design 0 November 9th, 2007 03:45 AM
Creating an INSERT INTO SQL statement for 2 tables sburgess Access 1 April 18th, 2007 08:52 AM
Temp Tables vs. Large Table DaveGerard SQL Server ASP 0 August 4th, 2005 10:08 AM
list all tables using SELECT Statement rylemer Access 5 March 7th, 2005 08:58 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.