One complex query vs Multiple simple queries

Question

What is actually better? Having classes with complex queries responsible to load for instance nested objects? Or classes with simple queries responsible to load simple objects?

With complex queries you have to go less to database but the class will have more responsibility.

Or simple queries where you will need to go more to database. In this case however each class will be responsible for loading one type of object.

The situation I'm in is that loaded objects will be sent to a Flex application (DTO's).

Answer 1

From a gut feeling I would say:

Go with the simple way as long as there is no proven reason to optimize for performance. Otherwise I would put the "complex objects and query" approach in the basket of premature optimization.

If you find that there are real performance implications then you should in the next step optimize the roundtripping between flex and your backend. But as I said before: This is a gut feeling, you really should start out with a definition of "performant", start simple and measure the performance.

Answer 2

The general rule of thumb here is that server roundtrips are expensive (relative to how long a typical query takes) so the guiding principle is that you want to minimize them. Basically each one-to-many join will potentially multiply your result set so the way I approach this is to keep joining until the result set gets too large or the query execution time gets too long (roughly 1-5 seconds generally).

Depending on your platform you may or may not be able to execute queries in parallel. This is a key determinant in what you should do because if you can only execute one query at a time the barrier to breaking up a query is that much higher.

Sometimes it's worth keeping certain relatively constant data in memory (country information, for example) or doing them as a separately query but this is, in my experience, reasonably unusual.

Far more common is having to fix up systems with awful performance due in large part to doing separate queries (particularly correlated queries) instead of joins.

Answer 3

I don't think that any option is actually better. It depends on your application specific, architecture, used DBMS and other factors.

E.g. we used multiple simple queries with in our standalone solution. But when we evolved our product towards lightweight internet-accessible solution we discovered that our framework made huge number of request and that killed performance cause of network latency. So we sufficiently reworked our framework for using aggregated complex queries. Meanwhile, we still maintained our stand-alone solution and moved from Oracle Light to Apache Derby. And once more we found that some of our new complex queries should be simplified as Derby performed them too long.

So look at your real problem and solve it appropriately. I think that simple queries are good for beginning if there are no strong objectives against them.