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