keeping running totals vs. calculating

[lution]

Background:
I currently have two tables, orders and payments. I need to track payments in their own table for accounting purposes. I was keeping a running tally of the balance in the orders table and updating it whenever a payment was made (I also set a paid date when the balance reached 0) but ran into a row locking issue when someone made a payment that covered 2 orders that were next to each other in the orders table if the orders form was open. So... I took the balance and paid date out and created functions to calculate them and display the calculation on the orders form now.

The problem is, I also have a past-due report to find orders that are more than 60 days old. What used to take a couple seconds to generate is now taking 30+ seconds for only 2500 orders because I have to calculate the balance for every order to see if its is 0. I have the primary, foreign, and where fields indexed for the query. Any recommendations on what I can do to get some of the speed back so as the orders continues to grow it doesn't turn into a day-long thing to generate?

Thanks, Lution

 

[pono1]

Background:
The problem is, I also have a past-due report to find orders that are more than 60 days old. What used to take a couple seconds to generate is now taking 30+ seconds for only 2500 orders because I have to calculate the balance for every order to see if its is 0...

Thanks, Lution

While many people warn against storing values you can calculate at any time, it's actually not uncommon to store, as you go, calculations in Accounting-Bookkeeping applications to get away from the performance issues you are experiencing. I recommend a slight re-design and the storage of relevant calculated numbers.

Regards,
Tim

 

[The_Doc_Man]

Several ways to do that. Simplest way: When an order balance due reaches zero, set a little yes/no flag in the order record for PaidInFull or whatever is your favorite way to say that. Then you can exclude paid up orders easily.

I tend to be against storing easily computed values. Your description makes me think there might be something odd about your computation, though. No WAY that a 2500 record database would be so sluggish unless you are choosing a really ugly way to compute everything.

I might compute the balance due on each order by doing two queries.

First query is a summation of payments grouped by order ID. So essentially, it is an order number and a total payment (sum of payment amounts).

Second query includes the order information including its avalue and the matching record from the first query that shows the total payment so far.

 

[Steve R.]

Can you distinguish active accounts versus closed accounts? If an account is not active, then it can be skipped? Also part of the criteria is that the accounts be greater than 60 days old. The implication is that you could skip records that are less than 60 days old.

To minimize computations, you could create a "filter" or "query" to only compute balances for the records that you are interested in. The rest are skipped.

Of course, there is the potential that there could be some unique cases that may need to be identified. An account "closed" due to inability to pay, where the person finally gets around to paying.

 

[gemma-the-husky]

how can you do this with only 2 tables?

surely you need a third payment allocation table

but anyway, either your order table carries a link to the payment table, or vice versa

in which case you can easily do a select query to find the unpaid orders - should be virtually instant