Re: Any way to speed up this query?

From: Kathy Weise (kw_at_kathyweise.com)
Date: 10/10/04


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
experiment.

Thanks again,

Kathy

"Kathy Weise" <kw@kathyweise.com> wrote in message
news:WMV8d.7597$M05.3176@newsread3.news.pas.earthlink.net...
> Rick & Leonid,
>
> I'll try both and report back which is faster.
>
> Thank you both very much.
>
> Kathy
>
> "Leonid" <leonid@NOgradaSPAM.lv> wrote in message
> news:%239AZ1j3qEHA.192@tk2msftngp13.phx.gbl...
>> 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,
>> table2.c
>>
>> will be faster?
>>
>> Leonid
>>
>> "Kathy Weise" <kw@kathyweise.com> wrote in message
>> news:4OE8d.3713$M05.2853@newsread3.news.pas.earthlink.net...
>> > Rick,
>> > I'm creating a file to output to a client. Table1 is their pristine
> data.
>> I
>> > 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
> their
>> > pristine data back with 3 appended fields. Yes, it's 1 to 1 so the new
>> table
>> > is only bigger than the original by the three added fields.
>> >
>> > Kathy
>> >
>> >
>> >
>> >
>> >
>> > "Rick Bean" <rgbean@unrealmelange-inc.com> 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
>> another
>> > system, or are you just using it for a query or report? If you just
>> > want
>> to
>> > "browse" or report on the resulting rows, then just using both tables
> with
>> a
>> > relationship should suffice. (I am assuming that this is a 1-1
>> relationship
>> > otherwise you might be pushing the 2gb max table size for the resultant
>> > table.)
>> >
>> > Rick
>> >
>> > "Kathy Weise" <kw@kathyweise.com> wrote in message
>> > news:ATz8d.3368$UP1.1497@newsread1.news.pas.earthlink.net...
>> > >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?
>> > >
>> > >
>> >
>> >
>>
>>
>
>



Relevant Pages

  • Re: same reseult set with less column
    ... For example the original query is "select x,y,z from table1 where ... How can I get the resultset of "select x,y from table1 where ... condition1" without knowing in advance the original query. ...
    (comp.databases.oracle.misc)
  • Re: same reseult set with less column
    ... For example the original query is "select x,y,z from table1 where ... How can I get the resultset of "select x,y from table1 where ... condition1" without knowing in advance the original query. ...
    (comp.databases.oracle.misc)
  • Re: same reseult set with less column
    ... For example the original query is "select x,y,z from table1 where ... How can I get the resultset of "select x,y from table1 where ... condition1" without knowing in advance the original query. ...
    (comp.databases.oracle.misc)
  • Re: Any way to speed up this query?
    ... I retested my original query on a recent file. ... Table1 ...
    (microsoft.public.fox.vfp.queries-sql)
  • Re: Error 1147 : Target is already engaged in relation.
    ... SELECT Table1 ... SET RELATION TO iID INTO Table2 ADDITIVE && error 1147 ... It is trying to set relation while first relation ...
    (microsoft.public.fox.helpwanted)