Filter data in SQL or in Java? [closed]

Filter on the backend (sql), whenever possible. If that makes the query too complex for a junior developer then so be it. While clarity of code is important, you shouldn't make design decisions based on how well a junior developer will understand it -- its sufficient that he be able to use it.

This is particularly the case when talking about different layers, your junior developer might not know any SQL, would you then avoid a SQL backend entirely?

Write your SQL to be as clear as possible (without sacrificing performance), but do so with the expectation that the person maintaining it will be familiar with SQL and how it should be used. When crossing layers like this, a little "easier to understand" can really kill your performance (pulling data back from the db in order to update it, can take thousands of times longer than a update on the DB, inappropriate use of a cursor can be expoentally worse than a set based solution).

If the data is already in the DB, then it makes more sense to do the filtering within it, since the RDBMS will be optimized for this kind of work. If the filtering can be confusing to novice and intermediate developers, why not hide it in a view, and only grant access to the view, to the users in question?

I guess there are no definite answers to it. It depends of individual use case. Both filtering in java as well as SQL can be applicable depending on the application under consideration.

• As you mentioned, filtering in SQL can make the queries complex and and costly. But at the same this can be improved using database tuning, putting appropriate indexes, table partitioning etc. This is specially the case where in the database design is still evolving and you can do these type of changes.
• if you are working on a system whose database is already designed and you have hardly any scope for significant changes (and hence not much query/database optimization), in this case filtering in java is better option.

It all depends on specific use case.