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.
######################################################################