Re: Any way to speed up this query?

From: Rick Bean (rgbean_at_unrealmelange-inc.com)
Date: 10/11/04

  • Next message: Rick Bean: "Re: fatal error query"
    Date: Mon, 11 Oct 2004 09:04:16 -0400
    
    

    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: Rick Bean: "Re: fatal error query"