Re: Any way to speed up this query?
From: Rick Bean (rgbean_at_unrealmelange-inc.com)
Date: 10/11/04
- Previous message: Rick Bean: "Re: sql problem"
- In reply to: Anders Altberg: "Re: Any way to speed up this query?"
- Next in thread: Kathy Weise: "Re: Any way to speed up this query?"
- Reply: Kathy Weise: "Re: Any way to speed up this query?"
- Messages sorted by: [ date ] [ thread ]
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?
>> >
>> >
>>
>>
>
- Previous message: Rick Bean: "Re: sql problem"
- In reply to: Anders Altberg: "Re: Any way to speed up this query?"
- Next in thread: Kathy Weise: "Re: Any way to speed up this query?"
- Reply: Kathy Weise: "Re: Any way to speed up this query?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
