extremely slow stored procedure. 2

[wmikeh]

I have a table called PurchaseOrder which contains the header information and a table called PurchaseOrderLine which contains the line information for purchase orders. I have set up an update/insert trigger on PurchaseOrderLine to update 4 different fields in PurchaseOrder - they are: TotalAmount(which is a sum of the amounts in purchaseorderline), IsManuallyClosed ( which is a flag that is set to -1 if all ManuallyClosed fields in PurchaseOrderLine are -1 and it is set to 0 if they are not), IsFullyRecieved(same as IsManuallyClosed except that it checks the received field), and AmountDue(this is a sum of the purchaseorderline rate field * the purchaseorderline ReceivedQuantity field). The trigger basically updates the purchaseorder table through user defined functions. OK... the problem is that when I run a batch insert into PurchaseOrderLine it times out. I tried to do an insert through vb code using ado... this works, but takes forever. I am trying to batch insert approx 27000 records into PurchaseOrderLine and have the trigger update the appropriate PurchaseOrder row but need a more efficient way of handleing this... Any suggestions.

 

[danvlas]

Hmmm... The best advice I have is to give up storing calculated values in a table and use stored procedures/views/functions to calculate them on the fly, then present the results to the application. It looks like some Group By clauses plus a few simple calculations would return what you need.

Triggers also should be avoided (if possible). Especially if they happen to fire themselves...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[wmikeh]

Thanks, I will probally do that. Quick question for you though... since I am new to programming in adp..... What are triggers usually used for?

 

[cmmrfrds]

I agree with danvlas, don't store aggregated data unless for performance reasons and then that should probably be done in a batch process.

I use triggers to store audit type information. On my application tables there are always 2 fields, user and modify date. The trigger updates these fields on any database modification.