[dba-SQLServer] An interesting tale

jwcolby jwcolby at colbyconsulting.com
Thu Nov 12 14:35:49 CST 2009


well... that is what I did.

Outer join where FieldOverThere is null

"Not in"

John W. Colby
www.ColbyConsulting.com


Francisco Tapia wrote:
> 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
>>
>>
> 



More information about the dba-SQLServer mailing list