C#•2y ago

✅ Calculate statistics on server or on frontend? Help me decide please! :)

Hi guys, could anyone help me decide how I should solve this problem? Right now, Instead of implementing pagination, I load all RunnablesData table rows for a specific API key and then have Javascript frontend functions make statistics shown on my frontend. (eg: total runnables created today) Should I have this statistics logic in the backend instead of the frontend? Some benefits of this I can think of: + Less transferred HTTP data. Don't have to load huge JSON, just a /GetStats/ and /LoadRunnables?page=1 endpoints with minimal data. Drawbacks: - Calculating stats would also require loading all objects from SQLServer into a list and then using that to calculate stats so database would have to do the same amount of work anyway (?) - Tiny bit more computational load on server side which is always best to do on frontend (not a big deal) Things to note: - No user would have more than 5k rows to load from RunnablesData table, so the JSON would have 5k rows maximum. - The backend server is powerful: 4 GB Memory / 2 AMD vCPUs / 80 GB Disk / LON1 - Ubuntu Can anyone help me decide what I should do? Table schema is attached 🙂 Thanks!

16 Replies

Pobiega•2y ago

Depends on your stats, but sending 5K records to the frontend seems... excessive. for the case of total runnables created today, thats one single sql query, returning a single value

Angius•2y ago

If it's just counting rows that were added since some date, etc, have the database do the heavy lifting

oeOP•2y ago

There's more stats, like all sum of all runnableAmount per day

Pobiega•2y ago

still, fetching every single record from the DB is likely a lot slower than just letting the DB get the stats for you so unless you need access to the individual records in the frontend for other reasons, I would calculate it all in the DB, then return it as a nice dto

oeOP•2y ago

Ok perfect I'm guessing that using the SUM() function on SQL server is quicker than loading all rows as it will only SUM the column values that you need instead of loading all the columns... but are there any other reasons? @Pobiega

Pobiega•2y ago

wdym "are there any other reasons"? yes, SUM etc are a lot faster

oeOP•2y ago

Ok Sick This discord is the best

Pobiega•2y ago

it is.

oeOP•2y ago

Like what other discord in the world can you get such quick help with thoughtful answers and smart people coming to the rescue No other programming discord has this good of a community

Pobiega•2y ago

You can /close the thread if you don't have any further questions.

oeOP•2y ago

in one of the percentage stats modules in JS, I calculate 3 things: for all runnabledata rows: 1 runnableAmount sum per day as a list (since the start of the year) 2 total runnableAmount this week 3 total runnableAmount last week i believe in TSQL i have to separate the 1 query from the 2 and 3 query. if I do have to separate those 2 as different queries, would that still be faster?

Pobiega•2y ago

yup you can ofc profile it to doublecheck

oeOP•2y ago

SELECT 
  SUM(CASE WHEN DATEPART(wk, runnableStarted) = DATEPART(wk, GETUTCDATE()) THEN runnableAmount ELSE 0 END) AS ThisWeekTotalAmount,
  SUM(CASE WHEN DATEPART(wk, runnableStarted) = DATEPART(wk, GETUTCDATE()) - 1 THEN runnableAmount ELSE 0 END) AS LastWeekTotalAmount
FROM RunnablesData
WHERE runnableType = 0

SELECT 
  SUM(CASE WHEN DATEPART(wk, runnableStarted) = DATEPART(wk, GETUTCDATE()) THEN runnableAmount ELSE 0 END) AS ThisWeekTotalAmount,
  SUM(CASE WHEN DATEPART(wk, runnableStarted) = DATEPART(wk, GETUTCDATE()) - 1 THEN runnableAmount ELSE 0 END) AS LastWeekTotalAmount
FROM RunnablesData
WHERE runnableType = 0

this is my 2,3 code ok

Pobiega•2y ago

but I'm fairly sure the act of fetching 5K records and ORM-mapping them will be a lot slower

oeOP•2y ago

yeah and do you think its possible to join all the queries into one? i feel it could be pretty complex but also acheivable since 1 would provide data for 2 and 3 right? i'd guess it would probably be the most efficient to combine them

Pobiega•2y ago

Yeah, since each query has an overhead

Gaming

Programming

✅ Calculate statistics on server or on frontend? Help me decide please! :)