Re: Any way to speed up this query?
From: Fred Taylor (ftaylor_at_mvps.org!REMOVE)
Date: Thu, 14 Oct 2004 18:43:51 -0700
> What is sys(3050,1,10e6) ?
Set foreground memory usage of VFP to be 10 million bytes.
-- Fred Microsoft Visual FoxPro MVP "Kathy Weise" <email@example.com> wrote in message news:rnCbd.firstname.lastname@example.org... > Not tight on disk space or memory. Keep in mind that each table is approx. > 1 Gb each, but combined fields are only a little more since the 3 added > fields are only a total of 14 characters. > > What is sys(3050,1,10e6) ? > > > > "Rick Bean" <email@example.com> wrote in message > news:eNmcRL5rEHA.1568@TK2MSFTNGP10.phx.gbl... > Anders, > 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. > > Rick > > "Anders Altberg" <firstname.lastname@example.org> wrote in message > news:OVLVf73rEHA.4004@TK2MSFTNGP10.phx.gbl... >>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" <email@example.com> wrote in message >> news:4OE8d.3713$M05.firstname.lastname@example.org... >>> 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" <email@example.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" <firstname.lastname@example.org> wrote in message >>> news:ATz8d.3368$UP1.email@example.com... >>> >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? >>> > >>> > >>> >>> >> > >