Re: Any way to speed up this query?
From: Rick Bean (rgbean_at_unrealmelange-inc.com)
Date: Mon, 11 Oct 2004 09:04:16 -0400
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" <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 )
> SELECT * FROM T1 LEFT JOIN T2 ON T1seqno=T2.seqno
> 114 seconds
> "Kathy Weise" <email@example.com> wrote in message
>> I'm creating a file to output to a client. Table1 is their pristine data.
>> 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
>> is only bigger than the original by the three added fields.
>> "Rick Bean" <firstname.lastname@example.org> wrote in message
>> I'm just curious why you need this table. Are you moving the data to
>> system, or are you just using it for a query or report? If you just want
>> "browse" or report on the resulting rows, then just using both tables with
>> relationship should suffice. (I am assuming that this is a 1-1
>> otherwise you might be pushing the 2gb max table size for the resultant
>> "Kathy Weise" <email@example.com> wrote in message
>> >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
>> > 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?