[dba-SQLServer] An interesting tale

Gustav Brock Gustav at cactus.dk
Thu Nov 12 07:50:58 CST 2009

Hi John

I have the same experience though in a much smaller scale - going from close-to-impossible to a snap; temp tables are perfect in some cases.


>>> jwcolby at colbyconsulting.com 12-11-2009 14:42 >>>
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

More information about the dba-SQLServer mailing list