Mark Breen
marklbreen at gmail.com
Fri Nov 13 00:55:04 CST 2009
Hello John, Another thing to add to your toolset of handy tips is to avail of the SELECT TOP command in SQL. I use that then I am about to launch a set of routines that I think may be slow. It enables me, in advance to know whether the SQL is efficient enough to run or whether I need to consider breaking it into two ore more steps. In your case, I would first write all the code with Select top 10, then expand that to top 100,000 or even 1M, and only if all looks Ok would I kick it off. Just a hint as to how I do larger transactions, HTH, Mark 2009/11/12 jwcolby <jwcolby at colbyconsulting.com> > 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 > >