[dba-SQLServer] An interesting tale

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.

/gustav

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

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