[dba-SQLServer] An interesting tale

Mark Breen marklbreen at gmail.com
Fri Nov 13 00:55:04 CST 2009

Hello John,

Another thing to add  to your toolset of handy tips is to avail of the
SELECT TOP command in SQL.

I use that then I am about to launch a set of routines that I think may be
slow.  It enables me, in advance to know whether the SQL is efficient enough
to run or whether I need to consider breaking it into two ore more steps.

In your case, I would first write all the code with Select top 10, then
expand that to top 100,000 or even 1M, and only if all looks Ok would I kick
it off.

Just a hint as to how I do larger transactions,



2009/11/12 jwcolby <jwcolby at colbyconsulting.com>

> 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

More information about the dba-SQLServer mailing list