jwcolby
jwcolby at colbyconsulting.com
Thu Nov 12 08:57:13 CST 2009
> I have the same experience though in a much smaller scale - going from close-to-impossible to a snap; temp tables are perfect in some cases. Yep, in agreement there. There must be some strange interplay with the export engine and the data engine though. If I can built a count query on top of the query I am about to use for export, and that count returns in about 6 minutes, there is no way on God's green earth it should take overnight to export 20 thousand of my 40 million records using that same export query. John W. Colby www.ColbyConsulting.com Gustav Brock wrote: > Hi John > > I have the same experience though in a much smaller scale - going from close-to-impossible to a snap; temp tables are perfect in some cases. > > /gustav > >>>> jwcolby at colbyconsulting.com 12-11-2009 14:42 >>> > 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? >