[dba-SQLServer] An interesting tale

Jim Lawrence accessd at shaw.ca
Thu Nov 12 11:46:15 CST 2009

Ahhhh, the old imfamous 'NOT IN' clause. Only good if you are going for a
coffee but in your case going camping. The other point you noted is that
even a 'SELECT * ...' would take a weekend unless indexed. ;-)


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, November 12, 2009 5:43 AM
To: Dba-Sqlserver
Subject: [dba-SQLServer] An interesting tale

I need to export 40 million records, from a table of 50 million records
where these records 40 
million records are not in another list.  So I build up a "not in" kind of
query to grab the PKs, 
and a count to verify I got the right things.  The count runs in about 6
minutes.  I join that NotIn 
to the table from which I am pulling the data to export and start the export
running (to a csv file).

This was yesterday afternoon.  By midnight, still no file.  By this morning
still no file BUT... by 
this morning the export dialog said I had managed to export 20 thousand
records so far.


So I go create a table to hold just the PK of the records NotIn the other
table.  Populate that with 
the PKs (took about 5 minutes), index it (took about 30 seconds) removed the
NotIn query and 
replaced with the NotIn table and start the export running.  Two minutes
later I have 40 million 
records in a csv file.

Obviously I am not real hip on the query analyzer, but would it or some
other tool warn me that 
something is going to take the rest of eternity to complete?

John W. Colby
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com

More information about the dba-SQLServer mailing list