[dba-SQLServer] An interesting tale

Francisco Tapia fhtapia at gmail.com
Thu Nov 12 15:45:47 CST 2009


so are you running this in query analyzer and it's taking for ever?  or are you exporting using some other tool and it's taking forever?

--
Francisco


On Nov 12, 2009, at 12:35 PM, jwcolby wrote:

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