Between code (C#, Java) and database (SQL) where should we sort or filter?

Question

Between code (C#, Java) and database (SQL) where should we sort or filter?

In code it means having a single stored procedure to retrieve all data from the database and then filter my data (in the business or data layer).

In database it means having a stored procedure. In this case the code (data layer) send my filter settings to stored procedure that takes care to return a lightened data.

What is better and in what circumstances?

Answer 1

It really depends on the nature of the usage. Example:

• Data size
• Fetch Frequency
• Response-time to user
• Etc etc

Generally, let the DB server do as much data related operation as you can, they are optimized for it. But it's not a definite rule. In many cases, batch of data are retrieved in one go, preseented and sort/filtered at the UI layer/application-side. E.g. For small table sorting on web pages for faster response-time.