[dba-SQLServer] An interesting tale

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. ;-)

Jim

-----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.

WHAT???

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
www.ColbyConsulting.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com