[FIXED] Is it better to count in server side API using java stream() then using count query call repeatedly in spring jpa

Issue

I want to count the number of rows in a table three times depending on three filters/conditions. I want to know which one of the following two ways is better for performance and cost-efficiency. We are using AWS as our server, java spring to develop server-side API and MySQL for the database.

1. Use the count feature of MySQL to query three times in the database for three filtering criteria to get the three count result.
2. Fetch all the rows of the table from the database first using only one query. Then using java stream three times based on three filtering criteria to get the three count result.

Solution

It'll be better to go with option (1) in extreme cases. If it's slow to execute SELECT COUNT(*) FROM table then you should consider some tweak on SQL side. Not sure what you're using but I found this example for sql server

Assuming you go with Option (2) and you have hundreds of thousands of rows, I suspect that your application will run out of memory (especially under high load) before you have time to worry about slow response time from running SELECT count(*). Not to mention that you'll have lots of unnecessary rows and slow down transfer time between database and application

Answered By - Tan Kim Loong