Thanks for the reply...

I altered both the update sql statement and the stored procedure as
suggested... Just reporting back on the changes (to summarize, there
were none :-) )

After 20 minutes of 100% cpu time with the update sql statement I
stopped the client and restarted the server (not sure how long it would
have taken).

I then ran the stored procedure, and it finished in 10.7 seconds. I
don't know why the update statement takes so long, there are only 9500
invoice records.

Also, thanks for your comment on our plans to bring the calculated
invoice sum up into the invoice table. This doesn't sit that easy with
me either and it is nice to here it from someone who really does know
what they are doing :-). We are actually doing more de-normalizing than
just that, we are also adding (or subtracting) the invoice (now
transaction) amounts to a summary table for the month for each debtor
contact.

Our queries on sales analysis are just running to slow.

Our design is now heading towards a debtor transaction table that has
invoices, receipts and journals in the one table. Hence why we decided
to also include the amount from the invoice (by adding up its line
items) in the new debtor transaction table (that used to be the invoice
table).

We don't have any OLAP type tools, I guess we are creating some tables
that are just used to speed client queries.

If I could ask another design question: I had planed to have triggers on
the line items and lines table that would update the invoice amount for
each change. But this would mean that the invoice amount would get
recalculated every time a line item was added. There could be possibly
100 line items, so this would mean that an update query in the trigger
would fire for every item. Haven't tested the performance of this yet,
the update would need to do a similar sum of line items as the queries
in the original question. At this stage I have backed off from this idea
as I thought the performance would be to bad, instead I am planning for
the client to include the amount for the invoice when it saves it...
Again, this doesn't sit very well with me either.

Sorry for the long message, if anyone gets this far in reading it, I am
just after some general suggestions (like, yes, put the triggers in
there, don't rely on the client, or, no, the triggers will be to slow!)
:-)

Thanks for any comments
Regards
Colin



-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Friday, 31 January 2003 7:29 p.m.
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] How do I rewrite this query to be fast??


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.

Just be very careful to use fully-qualified fieldnames in any
multi-table
operations (whether DSQL or precompiled as views or SPs) to avoid
getting
wrong results which may not be evident. If you don't provide it, the
fact
that a query runs fast (or runs successfully) doesn't mean, per se, that

you have achieved the intended result.

Here is your original query, corrected so that you get the desired
correlation between the invoice being updated and the one being queried:

Update jb_Invoice i
Set i.Amount = (
select
SUM(il.Quantity * ili.Invoice_Price)
FROM
jb_Invoice_Line il
JOIN jb_Invoice_Line_Item ili
ON
il.LINE_NR = ili.LINE_NR AND il.INVOICE_NR = ili.INVOICE_NR WHERE
il.Invoice_Nr = i.Invoice_Nr

Try this to see whether it is faster. However, I would consider
replacing
it with a stored procedure similar to your example - looping through the

providing set is the best way to do this update. A subquery is pretty
certain to be slower for this.

Now, here is your SP code, corrected to resolve ambiguities: For select
il.Invoice_Nr, SUM(il.QUANTITY * ili.Invoice_Price) FROM JB_INVOICE_LINE
il JOIN JB_INVOICE_LINE_ITEM ili ON il.LINE_NR = ili.LINE_NR) AND
il.INVOICE_NR = ili.INVOICE_NR Group BY il.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

To save a lot of typing and clutter, it's worth knowing that Fb/IB SQL
does
not require the INNER keyword. JOIN on its own is always an INNER
JOIN. Likewise, the OUTER keyword is also not needed. LEFT JOIN, RIGHT

JOIN and FULL JOIN imply outer joins in Fb/IB.

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

I don't like this kind of redundancy. Have you utterly proven to
yourself
that it will speed other queries sufficiently to justify the
synchronicity
problems you are giving your system by storing your detail values as a
hard
aggregate?

regards,
heLen



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