[dba-SQLServer] An interesting tale

Instead of using not in, use an outter join and it will return quicker.




On 11/12/09, Jim Lawrence <accessd at shaw.ca> wrote:
> 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
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>

-- 
Sent from Gmail for mobile | mobile.google.com

-Francisco
http://sqlthis.blogspot.com | Tsql and More...