Francisco Tapia
fhtapia at gmail.com
Thu Nov 12 09:36:03 CST 2009
So how long does the query take and what tool are you using when you say you are exporting? With query analyzer you just have to change the export location be it grid text or file. Query Analyzer will display an estimated execution plan so you can review what theengine wants to do such as doing a full table scan or an index scan. It is with these tools that you can add query optimizer hints if these things need to be analyzed. Sent from my mobile On Nov 12, 2009, at 5:42 AM, jwcolby <jwcolby at colbyconsulting.com> wrote: > 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 >