Re: Any way to speed up this query?
From: Kathy Weise (kw_at_kathyweise.com)
Date: Sun, 10 Oct 2004 22:00:58 GMT
The results are in. I retested my original query on a recent file. Table1
had 4.6 million records and table 2 had 3.6 million. It took 5 hours.
Then I tested Rick's and it took over 6 hours. Then Leonid's took over 8
hours. So after all that, I decided to cut both files in half and the
resulting query of half the data in each table took an unbelievable 6
minutes. Yes, it took time to divide and index the files, but the overall
savings in time was well worth it. I learned a big lesson in this
"Kathy Weise" <firstname.lastname@example.org> wrote in message
> Rick & Leonid,
> I'll try both and report back which is faster.
> Thank you both very much.
> "Leonid" <leonid@NOgradaSPAM.lv> wrote in message
>> Or may be
>> use table2 order seqno
>> select 0
>> use table1
>> set relation to seqno into table2
>> copy to table3 fields name, address, city, state,..., table2.a, table2.b,
>> will be faster?
>> "Kathy Weise" <email@example.com> wrote in message
>> > Rick,
>> > I'm creating a file to output to a client. Table1 is their pristine
>> > append some data to a working copy of their file (table2) in which I've
>> > parsed fields, address standardized, etc. At the end, they want just
>> > pristine data back with 3 appended fields. Yes, it's 1 to 1 so the new
>> > is only bigger than the original by the three added fields.
>> > Kathy
>> > "Rick Bean" <firstname.lastname@example.org> wrote in message
>> > news:uDL2bgxqEHA.3896@TK2MSFTNGP15.phx.gbl...
>> > Kathy,
>> > I'm just curious why you need this table. Are you moving the data to
>> > system, or are you just using it for a query or report? If you just
>> > want
>> > "browse" or report on the resulting rows, then just using both tables
>> > relationship should suffice. (I am assuming that this is a 1-1
>> > otherwise you might be pushing the 2gb max table size for the resultant
>> > table.)
>> > Rick
>> > "Kathy Weise" <email@example.com> wrote in message
>> > news:ATz8d.3368$UP1.firstname.lastname@example.org...
>> > >I have a query that looks sort of like this:
>> > >
>> > > Select table1.name, table1.address, table,1.city, table1.state,;
>> > > [and several more fields from table1], table2.a, table2.b, table2.c;
>> > > from table1, table2 where table1.seqno = table2.seqno;
>> > > into table3
>> > >
>> > > I have seqno indexed on both tables (both numerical and both 10
>> > characters).
>> > > Each table is close to1 Gb. The query takes 2 hours (if I haven't
>> > > just
>> > > defragged my HD which helps a little).
>> > >
>> > > Is there anyway to speed this up?
>> > >
>> > >