0

I have an excel like table with a should value and an is value for each day of a month:

descrip. |        | 01 | 02 | 03 | 04 |
_______________________________________
column 1 | should | 60 |  0 | 60 |  0 |
         | is     | 60 |  0 | 60 | 60 |
_______________________________________
column 2 | should |  0 | 15 |  0 | 15 |
column 3 | is     |  0 |  0 |  0 | 15 |

I need the values of this table for two purposes:

  1. Extract some statistics (total; should / is ratio; etc)
  2. Based on the values see if the actions (entry in column) are done
    • Just once
    • Once every week
    • daily
    • periodically

Should I get the statistics with SQL queries or calculate them with JavaScript based on the JSON response? What are the (dis)advantages

Some additional information:

I'm using SpringBoot with JPA and a PostgreSQL database.

Tables:

  • Chart
  • Column
  • Action (should / is values)

Here is a part of my JSON response:

"columns": [{
        "id": 12,
        "should": [{
            "id": 13,
            "date": 1438552800000,
            "min": 60
        }],
        "is": []
        }
    }]
devz
  • 243
  • 1
    How often does the data change, how often will it be queried and are there any other uses for this data as is? – Matthew Whited Sep 15 '16 at 20:31
  • Here a little restriction of JPA and native Sql result mapping http://stackoverflow.com/a/13013323/5934037. Can't remember if it still happens in JPA 2.x – Laiv Sep 15 '16 at 21:27
  • @MatthewWhited: 1) The date does change once or twice a day per different planning. In total this would be like 20-40 queries/day 2) The queries for the statistics are somewhere around 100/month 3) Previously the statistics were on the same page as the data input. But the statistics are now in an own page (Maybe later in a dashboard - but for this a data warehouse would be the better solution). – devz Sep 16 '16 at 11:41
  • Currently I'm using the native SQL / JPA approach. I've been able to map the results to non entity objects. – devz Sep 16 '16 at 11:43
  • 1
    Based on your volume you can probably get away with just querying over your data. Going though the added crazy for ETL or cubes probably isn't worth the cost. – Matthew Whited Sep 16 '16 at 13:56
  • I don't know postgreSQL so I'm not sure about an optimized query. On MS SQL server this could be done pretty easy with a CTE with a few aggregated functions. – Matthew Whited Sep 16 '16 at 13:58

2 Answers2

1

As @Ewan has said, it would be better to have these statistics already calculated (we have here different options. Ewan pointed to one of them).

Back to the question, here some disadvantages of each solution:

  • Client side: Depending on the complexity of the calcs and the volume of the data to retrieve; to load the page may take longer than usual. In mobile clients, it also has an impact on the memory and the battery (the charge is limited here). Then comes the fact that statistics should be calculated every time the page is loaded.

    Important to say that, if data is not persisted in any sort of cache, is quite easy to overload the DBMS just by clicking F5 like crazy.

    Summarising, it affects the UX, and in some cases, it has an impact on the resources (memory, battery, CPU, ...)

  • DB-side: DB is a shared resource. Some calcs may cause a serious problem of performance to the DBMS and any application connected to the DB is going to suffer the consequences.

    At this point is good to know the concurrency (possible number of simultaneous requests to the DB asking for the statistics) and the frequency of these requests. The volume of the data and its complexity also matters.

    Then comes the maintenance. Depending on the complexity of the SQL statements, the task could be dramatic.

    However, DBMSs are good at doing calcs, aggregates, etc. But you should be careful because you can end up taking down the DBMS (true story).

Laiv
  • 14,598
0

Neither, You should use a data warehouse, calculate your statistics via a MDX query and store the result.

your website can then present the stored results without having to recalculate on each page request.

Ewan
  • 75,506
  • 2
    You can calculate with either of the OP's methods and store the results for later use. Not sure why MDX would be required. It's nice if you know how to use it. – JeffO Aug 17 '16 at 16:56