Re: Any way to speed up this query?
From: Anders Altberg (x_pragma_at_telia.com)
Date: Fri, 15 Oct 2004 01:24:38 +0200
10E6 is the same as 10000000, SYS(3050,1|2, amount_of_memory) sets limits
on the amount of memory VFP grabs and uses. It's been shown that this can
have positive effect.
Your tables were five times bigger than the cursors I tested. You had a
RECSIZ() of 250 bytes while I only used 50. That should make some
"Kathy Weise" <email@example.com> wrote in message
> Not tight on disk space or memory. Keep in mind that each table is approx.
> Gb each, but combined fields are only a little more since the 3 added
> are only a total of 14 characters.
> What is sys(3050,1,10e6) ?
> "Rick Bean" <firstname.lastname@example.org> wrote in message
> I think that one big delay may be Kath'y writing it out to a new table.
> Especially if she is tight on memory AND diskspace, the machine could be
> "thrashing" to get the space to complete the task.
> "Anders Altberg" <email@example.com> wrote in message
> >I still don't see what's slowing this down.
> > I created two cursors with three columns each , a record length of 50
> > bytes
> > and index on the PK seqno column; one cursor with 4.6 million records,
> > the
> > other 3.5 million rows with matching 'seqno' keys.
> > (512MB memory, 1.5Mhz processor )
> > SYS(3050,1,10e6)
> > SYS(3050,2.10e6)
> > SELECT * FROM T1 LEFT JOIN T2 ON T1seqno=T2.seqno
> > 114 seconds
> > -Anders
> > "Kathy Weise" <firstname.lastname@example.org> wrote in message
> > news:4OE8d.3713$M05.email@example.com...
> >> Rick,
> >> I'm creating a file to output to a client. Table1 is their pristine
> > 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" <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
> > another
> >> system, or are you just using it for a query or report? If you just
> > 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" <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
> >> > defragged my HD which helps a little).
> >> >
> >> > Is there anyway to speed this up?
> >> >
> >> >