What is the faster way of counting? Using count query in the database or counting retrieved rows programmatically?
I was wondering what would be a faster way as far as counting rows is concerned from a database? Is count query in DB (server) going to be faster than some sort of count in a program (client). I think the things that are to be considered are
Size of the database
Programing language use
Are the rows that are to be counted required by the client?
Just the counting by itself cannot be made any faster than executing it in a compiled language like C++, C# or Java. Counting through a stream of rows is nothing.
Unfortunately, copying the rows from the server to the client is enormously more expensive that counting them. So you are always better off letting the server count them.
I literally cannot think of a scenario in which one should count on the client (if the only thing required is the count, not the rows themselves).
If the rows are required on the client:
- If all rows are required: Count on the client
- If only a single page is required: Count on the server! There might be hundreds or thousands of pages and you don't want to download all of them. The RDBMS can often optimize counting because no column data is required to count.