Re: Any way to speed up this query?

From: Anders Altberg (x_pragma_at_telia.com)
Date: 10/11/04


Date: Mon, 11 Oct 2004 11:00:16 +0200


"Kathy Weise" <kw@kathyweise.com> wrote in message
news:uiiad.8583$UP1.7852@newsread1.news.pas.earthlink.net...
> 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?
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>
>