Re: Missing table entries
From Walt Hultgren {rmy}:
*
* In article <366u06$6c7@emory.mathcs.emory.edu> rob@dssmktg.com (Robert Minter)
* writes:
* >From irl3b2c.att.com!bt@uunet.uu.net:
* >*
* >* Does anyone know of a way WITHIN sql for identifying missing entries in a
* >* table?
* >* I can see the missing rows if I do something like:
* >*
* >* select table1.code,
* >* table2.code
* >* from table1, outer table2
* >* where table1.code=table2.code
* >*
* >Try just like you stated:
* >
* > SELECT code FROM table1
* > WHERE code NOT IN ( SELECT code FROM table2 )
*
* As an interesting aside, we have found here that using an approach that
* avoids NOT IN or NOT EXISTS results in much faster execution times.
*
* In essence, you create a temp table of codes from the master table. The
* temp table also contains a flag column to indicate which codes are found
* in the detail table:
*
* select code,
* 0 flag
* from table1
* into temp tflag;
*
* Then, mark all of the codes found in the detail table:
*
* update tflag
* set flag = 1
* where code in ( select code
* from table2
* where tflag.code = table2.code );
*
* Then, report all of the codes from the temp table that weren't marked:
*
* select *
* from tflag
* where flag = 0;
*
*
* You'd think that all of this processing would take longer, but we have
* found that not to be the case.
*
* The fact that this method is much faster may just be a quirk of our
* database design or tuning. However, I've had good results with this in
* several similar but unrelated situations. Maybe the first approach would
* work as well if we indexed *everything*, but that's a bad idea obviously.
*
*
* As an aside to the aside, we came across this scheme while trying to
* improve the execution time on a script that looked for missing rows. I had
* originally started trying to improve things by investigating the execution
* times of a script using NOT IN versus one that used NOT EXISTS. The
* method I described above worked so much better, I stopped tinkering with
* the two NOT's before I found any clear indication that one might generically
* better than the other. If anyone can give me any guidelines in that area,
* I'd appreciate hearing from you.
Looks like it depends on the system. I did the following 2 SQL tests
on my system with 24343 records in table A and 49734 records in table B.
Both tables have an index with the primary field as code.
SQL Test 1:
select CURRENT from systables where rowid = 1;
select count(*) from table_a
where code not in ( select unique code from table_b );
select CURRENT from systables where rowid = 1;
The result is:
(expression)
1994-09-26 15:23:45.290
(count(*))
15436
(expression)
1994-09-26 15:29:26.710
SQL Test 2:
The result is:
(expression)
1994-09-26 15:48:42.090
(count(*))
15436
(expression)
1994-09-26 15:53:31.830
So, they both turn out to be arund 6 minutes.
Robert Minter |Data Systems Support| \\\\\\_///
Programmer, Software Development | Orange, CA | ( _ _ )
internet: rob@dssmktg.com | Tel: 714.771.0454 | (| ^ |)
bangpath: uunet.uu.net!dssmktg!rob| Fax: 714.771.3028 | \\`-'/
#include <disclaimer.h> SURF'S UP \\_/