-1

I have a table with transactions in my DB, here is an example.

# id, user_id, type, eur_amount, usd_amount, convert_currency, convert_currency_amount, eur_exchange_rate, usd_exchange_rate, payment_source, table_source, table_source_id, package_id, package_name, deleted, date_elem, date_added, date_modified
2, 1803, membership, 60.19, 59.82, EUR, 60.19, 1.000000, 0.993912, website, users_transactions, 2, 103, 1 Monat, 0, 2022-08-22, 2022-08-22 13:03:50, 2022-11-07 11:43:25

The user can use a filter to get the amount of payments by day.

For example he can request the data between two dates.

On the server I can just select all the data between two dates and return the object array to the frontend. The frontend then has to loop over the objects and create the analyics by adding the eur_amount into one total value for each day.

I could also do all the work on the server with a groupBy etc., so the frontend does not have to do any calculations.

What is the better practise? What has better performance?

Roman
  • 103
  • 3

2 Answers2

3

This is exactly the kind of "heavy lifting" that your database is built for.

select all the data between two dates ... do all the work ... with a groupBy

Bingo!

If you retrieve all the rows, send them to the client and let it do the aggregation, then you're sending many, many times the number of rows across the network, which will be slower, and you're relying on the client to do the hard work - it simply may not be powerful enough to do the job [well].

It is generally considered "better" to do the bulk of the work server-side if you can.

Phill W.
  • 8,706
  • 1
  • 11
  • 21
  • 1
    To add to this, the other advantage of having the database do the calculation is: it will never be screwed up by any changes to the front end. Calculating amounts is pretty critical, nice not to have that at the mercy of JS. –  Nov 07 '22 at 14:20
  • Thanks guys, very helpful! – Roman Nov 07 '22 at 15:44
0

Another benefit of doing the data transformations in the database layer is that it centralizes the code, results, and shape of the data to one place. You may later on decide you want to consume the same transformed data in some other report or application besides your main application, and having it centralized in the database makes it easier and more consumable that way.

J.D.
  • 37,483
  • 8
  • 54
  • 121