Re: Any way to speed up this query?

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

  • Next message: Tom Edelbrok: "SQL query to open OLEDB or ODBC to VFP?"
    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
    difference.
    -Anders

    "Kathy Weise" <kw@kathyweise.com> wrote in message
    news:rnCbd.4159$gy1.1960@newsread1.news.pas.earthlink.net...
    > 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" <rgbean@unrealmelange-inc.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" <x_pragma@telia.com> 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" <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?
    > >> >
    > >> >
    > >>
    > >>
    > >
    >
    >


  • Next message: Tom Edelbrok: "SQL query to open OLEDB or ODBC to VFP?"

    Relevant Pages

    • Re: Any way to speed up this query?
      ... Not tight on disk space or memory. ... >> Rick, ...
      (microsoft.public.fox.vfp.queries-sql)
    • Re: What Basic compiler should I buy??
      ... >except where things are very tight. ... It's the application requirements that count, not the language. ... implicitly decides how much memory is used, not the compiler. ...
      (comp.arch.embedded)
    • Re: Any way to speed up this query?
      ... Set foreground memory usage of VFP to be 10 million bytes. ... "Kathy Weise" wrote in message ... > Not tight on disk space or memory. ... >>> Rick, ...
      (microsoft.public.fox.vfp.queries-sql)
    • Re: Restarting killed processes
      ... > That sounds like your system is running out of memory; ... How much swap do you have? ... I ran free and memory was pretty tight. ... Mandrake and SuSE on a desktop with 384 Meg RAM and 40 gigs disk space, ...
      (Debian-User)
    • Re: Please help me!
      ... if you're really tight on memory (i.e. you can only use ... > registers for this calculation), ...
      (comp.programming)