I think the problem is in Indexing.If you give the proper primary key
,foreign key reference and index, then this wouldn't take more time.
And one more think,suppose if you use this update query in the
storedprocedure, try to recreate the procedure(Drop it and create it again
after indexing).Surely you will get a good speed.

Sivaraman



At 06:07 PM 31/01/2003 +1300, you wrote:

>Hi all,
>
>I have an update query which updates a table (currently only with 9,500
>records) based on the results of another query.
>
>The structure is basically an invoice table with an invoice_line table
>and an invoice_line_item table. We are bringing the amount of the
>invoice up into the invoice table to speed other queries.
>
>The query I have tried takes for ever (killed it as it tied up the
>server (100% cpu) for > 30 minutes annoying a few other users!) looks
>something like this:
>------------------------------
>Update jb_Invoice
>Set Amount = (
> select
> SUM(jb_Invoice_Line.Quantity * jb_Invoice_Line_Item.Invoice_Price)
> FROM
> jb_Invoice_Line INNER JOIN jb_Invoice_Line_Item ON
> (jb_Invoice_Line.LINE_NR = jb_Invoice_Line_Item.LINE_NR)
> AND (jb_Invoice_Line.INVOICE_NR = jb_Invoice_Line_Item.INVOICE_NR)
> WHERE
> jb_Invoice_Line.Invoice_Nr = jb_Invoice.Invoice_Nr
> )
>------------------------------
>I don't know why it takes forever. I created a stored procedure which
>(as far as I can tell) does the same thing and it only takes a few
>seconds (phew!). How do I rewrite my query so that it performs better?
>
>The stored procedure is shown below:
>------------------------------
> For
> select
> JB_INVOICE_LINE.Invoice_Nr,
> SUM(QUANTITY * Invoice_Price)
> FROM
> JB_INVOICE_LINE INNER JOIN JB_INVOICE_LINE_ITEM ON
>(JB_INVOICE_LINE.LINE_NR = JB_INVOICE_LINE_ITEM.LINE_NR) AND
>(JB_INVOICE_LINE.INVOICE_NR = JB_INVOICE_LINE_ITEM.INVOICE_NR)
> Group BY
> JB_INVOICE_LINE.Invoice_Nr
> Into
> :InvoiceNr,
> :Amount
> do begin
> Update Ct_Debtor_Transaction
> set Ct_Debtor_Transaction.Amount = :Amount
> Where Ct_Debtor_Transaction.Invoice_Nr = :InvoiceNr;
> end
>
>Regards
>C Fraser
>
>
>######################################################################
>Attention:
>The information in this email and in any attachments is confidential.
>If you are not the intended recipient then please do not distribute,
>copy or use this information. Please notify us immediately by return
>email and then delete the message from your computer.
>Any views or opinions presented are solely those of the author.
>######################################################################
>
>Yahoo! Groups Sponsor
>ADVERTISEMENT
><http://rd.yahoo.com/M=233351.2876045.4223503.2848452/D=egroupweb/S=1705115386:HM/A=1341247/R=0/*https://www.gotomypc.com/tr/yh/grp/300_mapG/g22lp?Target=mm/g22lp.tmpl>25018b.jpg
>
>250282.jpg
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to the
><http://docs.yahoo.com/info/terms/>Yahoo! Terms of Service.

[Non-text portions of this message have been removed]