0

I have several million booking rows in a table and would now like to save the totals of the booking amounts in another table depending on the customer, account no., product, etc.

Should this necessarily be done at application level, or can the database (MySQL InnoDB) also do this reliably with INSERT INTO SELECT? I could imagine that the database might have a performance advantage because you don't have to read the data from the database chunk by chunk. In addition, I would have to mark (UPDATE) each data record that I have already processed in order to continue at the correct point if the program terminates.

How would you solve this?

1
  • 3
    You should be able to do this entirely in the database, and simple aggregations over just millions of rows should complete almost instantly. Just ensure that you have an appropriate level of skill to cover whatever development you propose, since databases can be extremely important and disruption to their workings can have serious effects for a business.
    – Steve
    Dec 25, 2023 at 12:01

2 Answers 2

4

Should this necessarily be done at application level, or can the database (MySQL InnoDB) also do this reliably with INSERT INTO SELECT?

It is reliable, until it is not.

Honestly, this all depends on how complex your calculation logic is and how well it can be implemented in SQL. Your vague description

save the totals of the booking amounts in another table depending on the customer, account no., product,

sounds like something which can be implemented perfectly with a few simple group-by SQL statements. "Several million" booking rows are no order of magnitude which should be a huge problem for most contempary database servers when the subtotals can be calculated by a linear scan. However, you may not have told us the full story here, maybe your real requirements are more complex - only you know.

If I were in your shoes, I would simply try this out with SQL, which is probably the most straightforward and simple solution. In case you need to join in other, related tables, you have to care for proper indexing, of course. When this does not work well, runs too long etc, you can still switch to something more complicated.

I would have to mark (UPDATE) each data record that I have already processed in order to continue at the correct point if the program terminates.

Before going that route, I would first try if you can implement this without any markers - this is simpler and may be fully sufficient. Start by creating the aggregated tables in a single transaction. If the server terminates unexpectedly during execution, the transaction should be rollbacked, and nothing bad happens - you can simply retry the operation at a later point in time. If you need more than one transaction for filling an aggregate table, implement this in an idempotent way, where you can simply repeat the whole process and get always the same result. In this case, it might be sufficient to clear an aggregate table before refilling it. Or, you try to define the transactions which fill your aggregates so you can determine from the content of the aggregate table which data was already processed successfully and which not. Adding additional status columns or tables to the source data for tracking former processing steps is an optimization which has the potential to proof itself premature and unneccessary.

When you really run into one of the potential issues scetched in Ewan's answer, then it is still time enough to switch to something more complicated and try out if a solution implemented at "the application level" will really behave better. Don't forget getting the data out of the database and the results back into it introduces some extra overhead - in terms of running time, resource usage and programming effort - which needs to be balanced by some real benefits.

See also: If there are two ways of approaching a task, how should one choose between them?

0

Although it seems like you could do this purely in SQL it's generally not advisable for the following reasons.

  1. The business logic in one of your calculations becomes too complex to handle in SQL. For example, "make a geolocation api call to convert the postcode into lat long" or "send an email". Even if you don't have this at the moment, it could be a factor later on.

  2. Performance. Although the process itself will run fast when done purely in SQL, it won't be "performant". The whole operation will be done in a single transaction, potentially taking up all the resources of the database server for the duration and breaking other operations. A failure half way through may leave you with an unknown result with some records changed and some not.

    If done in code, you can run each insert individually, the entire operation may take longer, but your database will remain up and servicing other calls for the duration.

  3. Error handling. Although SQL does allow some error handling, what seems like simple insert this select, to begin with, can become far more complex with loops and child sprocs when you have to handle individual rows erring and being stored in some errored row table for reprocessing

  4. Testing. SQL is generally harder to write unit tests for. If you put the logic in code, each individual function can be tested

32
  • 5
    These are all very questionable assertions indeed. Certainly (2) is completely unfounded, since there is no significant obstacle in SQL to breaking up a batch of work into individual transactions, and the transactional guarantees the database offers will prevent inconsistency under all circumstances (or if intractable performance problems emerge, it will demonstrate the contradictions of your design).
    – Steve
    Dec 25, 2023 at 11:48
  • 1
    Loops are considered a bad practice in SQL when a single-statement alternative exists, but if your whole argument rests on saying that a single statement isn't appropriate for this case (for example because of the duration of the locks taken, or the resources consumed for transactional consistency of the entire batch), then obviously looping in SQL then becomes a legitimate solution. I certainly agree it's a complicated judgment.
    – Steve
    Dec 25, 2023 at 12:13
  • 1
    The complexity of any calculations may be a reason to palm it off to a different language. But honestly, looping in its own right is not a bad practice if it is (a) still a correct solution, and (b) done specifically to fall back from a correct single-statement solution which is too demanding for the context. The reason loops are suspect in SQL is because new practitioners either (a) use them as a first resort (inefficiently, and often incorrectly), or (b) use them incorrectly in a way that doesn't preserve an appropriate transactional consistency.
    – Steve
    Dec 25, 2023 at 13:24
  • 1
    I'd say it's very controversial.
    – Steve
    Dec 25, 2023 at 16:20
  • 1
    I agree tribalism is not a justification. And if you already have a client application, then that makes putting the logic there more reasonable than it would be otherwise. But it's very difficult to make sweeping statements about performance, reliability, or any other factor. Circumstances are so various that the context always counts. I'm reluctant myself to be more specific in the rebuttal, because of the complexity of setting up the context and covering my own backside!
    – Steve
    Dec 25, 2023 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.