Performance according to box control modeling

Asked

Viewed 126 times

3

I am making a system for daily box control, which allows launches/deletion of values. Below is the table that will store the values, filled with some data to exemplify. There are more fields, but for the doubt in question I believe it is not important to inform.

Table 'Movement':

+--------------+-----------------+------------------+-----------------+
| id_movimento | data_movimento  | tipo             | valor           |
| 1            | 26/03/2019      | 'R'              | 1000.00         |
| 2            | 27/03/2019      | 'P'              | 200.00          |
| 3            | 28/03/2019      | 'P'              | 100.00          |
+--------------+-----------------+------------------+-----------------+

'R' indicates receipt and 'P', payment.

The initial balance and the current balance are stored in another table, shown below.

Table 'Account':

+--------------+-----------------+------------------+
| id_conta     | saldo_inicial   | saldo_atual      |
| 1            | 5000.00         | 5700.00          |
+--------------+-----------------+------------------+

It will be made approximately 10 daily releases, because the customer movement is not so great. Per month, will be on average 300 launches. In 5 years, they will have 18000 launches in this table.

All the cash has a previous balance (formed by the sum of all the entries from the months prior to the current month), a period balance (formed only by the sum of the entries from the consulted period), an accumulated balance (sum of the previous balance and the entries from the period)and a current balance (sum of everything to date). The current balance of the account is updated in the 'Account' table at each launch/deletion.

My idea is that, for the previous balance, I will have to make an SQL query with a SUM of all data type 'R' of the table 'Movement' subtracting from a SUM of all data of type 'P' of the table 'Movement' from the initial date until the last day of the month preceding the current month being consulted.

Doubt

Since with a few years of use, there will be enough rows in the table (for example, in 5 years, 18 thousand releases), what would be the performance of such a query? Would it take too long for the database to make this calculation? There is a more correct way to be made a cash control?

mysql sql database query performance
  • 3

    18,000 records is nothing extraordinary. In principle you will have no problem with the query. If you had 18.000.000 yes, then you could discuss ways to optimize to the maximum.

  • I get it. Probably one day you will have that amount, but it will take time. Thank you João.

  • 1

    If the BALANCE table has the balance date , the partial balances would be recalculated from it (a precedent for example) but given the few entries it may not be necessary

  • Thank you @Motta. Should I create a 'historico_balances' table with the foreign key pointing to the account id and the current and date balance fields? And then whenever a release is made/deleted in the 'movement' table, include a new record in the 'historico_balances' table' ?

  • No, some process takes the last balance and calculates the next one based on the later releases. Use based on what erps generally do.

1 answer

3


This amount a day can have is ridiculously low, and everything it has shown even in very large quantities does not seem to cause any problem. Even if more or less serious configuration and modeling errors are made it should not cause so much damage, although it is better to do everything right to get maximum performance. Even in a linear complexity operation it should do the operations quoted in a fraction of a second on an SSD (in an HDD it may take a few seconds, but only if something is very wrong).

In general people have unrealistic expectations of scale and want the system to be able to handle volumes that never happen or that these volumes are so heavy.

I’m not sure I understand, but does it look like you’re trying to calculate the balance every time? In general this is not how it is done, you make a release and create a new balance in a table of balances, so you have a history of balances. Even if you don’t want to do this at least the account balance is updated every time with the existing balance and new release, you don’t need to pick up other releases, which is what was described in the question. Then I may not have understood the question of the previous balance.

Browser other questions tagged mysql sql database query performance

You are not signed in. Login or sign up in order to post.