Hi Guys,
I am quite an expirienced developer with Enterprise Application (8+ years)
I am asking my self a philosophic question: suppose I have to get some graph of objects from the DB but there are some objects in the graph that need to be filtered. What is better, to let the DB filter them for me and doing it via SQL/HQL or to get all the graph from the DB and then filter it via Java/C#?

For the discussion, lets suppose that the graph includes several thousands of objects and bringing them from the DB, as a whole, is not that heavy…

I know most of us grew up on SQL queries, but we all know the benefits of doing logic (filtering is logic) via object oriented.

what do you think?

howdy,

I would personally prefer to do it on database side if I can. However, in your example the filtering algorithm is the key. Since the network transfer is not big enough so that we penalize Java/C# side's filtering, I would say in your case the question is how hard it is to maintain the Java/C# code versus how hard it is to maintain the sql code. I would blindly say the SQL code is easier as per my experiences.


Farzad-

Hi Fazard,

As for your question: "how hard it is to maintain the Java/C# code versus how hard it is to maintain the sql code". I think that maintianing Java/C# code is more convenient, for many resons:
1 - you can debug each step in it
2 - it is more intuative for most people
3 - it is an object oriented language with all its benefits
4 - if you cache the objects, you don't have to do round trips to the DB and you don't overload the DB.
5 - you are less coupled to a relational data source. Maybe your data source in the future will be an XML, Flat Files, LDAP etc, which don't support SQL/HQL?

Frankly, for now, it is just a passing thought and i am not even sure i stand behind it...

As I read your email I get the feeling you are singling out a hard-to-do-it-in-SQL filtering which is indeed better off to be handled in a more general programming language than a DSL such as SQL/HQL. In many cases, as per my experience, it is easy to understand what a SQL does because of its simple and restrictive nature. I agree with your point about caching but caching is a little bit tricky when it comes to clustering and scaling. In these cases I usually prefer that I have a single point of holding the truth. I understand caching can be useful in a clustered environment if proper cache distribution mechanisms are used but still this is a hell of a lot of complexity when it comes to design since you really need to think of use cases when you decide how you want to cache objects and which objects should be cached and distributed.

Regarding the 5th point, do you really need that flexibility? As per my understanding we are coupled with multiple technologies in a project and replacing a technology is not completely painless anyways (in practice). That is because we always have two parties in a project (even when the two parties are the same people). There is one side that pushes technology and there is the other side that pushes business and business value. Usually everything finishes in such a way as to maximize business value, or the project dies away. We all know nice and clean code does not favor over business value and how much money a project can make.



Farzad-

Since we're all .Net people here (NHibernate), I'll make these points:

- With a good ORM (like Hibernate), filtering in C# = filtering in SQL. Hibernate is creating the equivelant query in SQL for you.

- With the introduction of LINQ (not LINQ to SQL, but the essential ability to query in memory collections), I think it's becoming far more obvious that querying is coming into its own for programming languages (at least on the .Net side, anyway).

I did data modeling for a long time; I never thought I would want to see dynamic sql. But out of 500 or so classes, I have a handful that are bypassing hibernate and doing it the classic way (stored proc, sqldatareader, foreach, etc). Typically this is for the few places where it's still much more efficient to let SQL do the work, like in some paging+full text searching scenarios.

As a rule though, I'd absolutely favor letting my ORM handle the glue code, as it should. If I need to bypass it when it makes sense, no one's holding a gun to my head to *only* use Hibernate.