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 >