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

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.