Unmatched query between 2 very large tables using 2 fields

[sschrupp]

I have two tables (Current and Prior), each with close to 2 million records, in two separate databases. With just the one table in each database the two databases are sized around 1.5 GB each. That's after compacting. I use a 3rd database that links to them among other terribly large databases. Yes, I know, we need SQL Server and are trying to get corporate to allow us to have tools like that besides only Access 2003. Until then we have to try and make do.

I'm trying to run an unmatched query to find the records in the "Current" table that aren't in the "Prior" table so I know which records are new this month. In order to match the records properly I need to match two fields, "Account" and "System".

The unmatched query wizard of course only lets you set up 1 field match, so I manually changed the query to include the 2nd field match as well. Just running a select query at this point, not an update, to see how many matches we have takes a LOOOOOONG time. And we aren't getting the results we expect at all. Granted our expectations might be incorrect, but I'm wondering if I'm doing this wrong.

What I have currently is:

SELECT Current_Details.AcctSchd, Current_Details.System
FROM Current_Details LEFT JOIN Prior_Details ON (Current_Details.System = Prior_Details.System) AND (Current_Details.AcctSchd = Prior_Details.AcctSchd)
WHERE (((Prior_Details.AcctSchd) Is Null) AND ((Prior_Details.System) Is Null));

Is that correct? Is there a better way of doing this considering how huge the tables are? Obviously using recordsets with dLookups is out of the question here.

I thought maybe having 2 fields to match was causing confusion so I attempted a query that tried to match one concatenated field in each table and that failed miserably. As did running queries with nested SQL queries.

Thanks in advance if anyone can shed any light on this for me. I have a feeling I'm just bumping our heads on a technological wall though. Here's hoping the SQL Server fairy makes a visit soon!

 

[SydneyGeek]

Build it graphically.
Once the wizard has created the initial query, go into Design view.
Add the second join, adjust its type so the arrow points the same way as the first.

To speed up the performance, make sure that the 2 join fields in both databases are indexed.

Denis

 

[sschrupp]

Thanks Sydney, that is what I did. Turns out they forgot to tell me about certain exceptions which brought the results from 400k+ to 20K+.

As for the indexing fields my worry is that the more indexes we have the longer it takes to import the data, to update the data, to move the data to other tables, etc. And I have to run probably around 25 different queries, many of which deal with different fields so it doesn't seem very feasible to start indexing a bunch of the fields since we're dealing with nearly 2 million records.

Thanks for the tip though, I might have to play around with it a bit to see if there might be a couple fields that I can index that'll help more queries than others without slowing down all the importing, updating, and moving too much.

 

[SydneyGeek]

You're right, indexing everything is a potential hassle when you pull data into indexed fields. But for searching, eg via joins or WHERE clauses, indexes will speed you up.

I have seen an article where dropping the indexes from the local table sped up the import dramatically; they then rebuilt the indexes once the records were in, to speed up subsequent operations.

Denis