SQL RDBMS : one query or multiple calls

Question

After looking around the internet, I decided to create DAOs that returned objects (POJOs) to the calling business logic function/method.

For example: a Customer object with a Address reference would be split in the RDBMS into two tables; Customer and ADDRESS. The CustomerDAO would be in charge of joining the data from the two tables and create both an Address POJO and Customer POJO adding the address to the customer object. Finally return the fulll Customer POJO.

Simple, however, now i am at a point where i need to join three or four tables and each representing an attribute or list of attributes for the resulting POJO. The sql will include a group by but i will still result with multiple rows for the same pojo, because some of the tables are joining a one to many relationship. My app code will now have to loop through all the rows trying to figure out if the rows are the same with different attributes or if the record should be a new POJO.

Should I continue to create my daos using this technique or break up my Pojo creation into multiple db calls to make the code easier to understand and maintain?

Answer 1

You should put correctness first. Create your data structures so that they model the domain in question in a correct and effective way that makes it easy for your code to work with.

Beyond that, try to minimize database calls, especially if the database is not local (residing on the same machine as the program calling it). Network latency is a real consideration here, and it can be non-trivial.

Let's say you have an operation that requires 10 database calls. If your network latency is 100 ms, this operation will take 1 second of pure overhead just communicating with the server, in addition to whatever amount of time it takes to actually do the work involved. If your latency is 1 second, it will waste 10 seconds on network latency alone. But if you get that down from 10 calls to 1, suddenly even in really ugly latency conditions, you're not wasting much time on network overhead.

As a general rule of thumb, if you're just retrieving data simply (and not doing heavy processing of the data inside the database server or on the client), the biggest bottleneck by far in a system with a non-local database will be network latency. So if you can reduce the number of calls, even if it means you need to do extra work on the client side once you've retrieved it, you'll still probably come out ahead.

As always, remember the most important rule of optimization: measure first! Optimize by hard data, not by rules of thumb like the one I just described, or you could easily end up doing a lot of hard work that slows things down! But in general, keeping the number of queries down is usually the best route.

Answer 2

I suggest you consider using DTOs after you have performed the correct join on the data source using SQL (if you are using RDBMs). In addition, you can also utilize the Multiple Result Sets concept to return different sets of objects in 1 server call if necessary.

DTO is not POJO (see:Difference-between-DTO-and-POCO - I know you are not asking about .NET but the concept is likely to be similar for this argument.)

Using this approach you will communicate less data between the server and the client and you will not have to do joins in the client logic manually specially, that to my knowledge, Java does not provide an equivalent of .Net's LINQ.

Also, consider using (or getting to know about) a good ORM. A good ORM will most likely offer you a practical approach.

Answer 3

If the query is returning more rows than expected then the query if badly written.

You have to run a query to get the data for Customer POJO. That query should return a single row, so the DAO populates a single POJO.

Then a second query is run to get the rows from the "many" table , given the foreign key ( customer ID ) and create as many POJOS as rows are found. Then that list of address objects is assigned to the customers POJO.

Customer class has a member of type List<Address>.

If the DB access is expensive, you can do it lazily, that's to say, fetch the addresses only when getAddress of getAddressList is first called.

That same technique can be used to all aggregations or compositions in your Customer class that are read from the DB.

EDIT:

About performance: Customer table must have a PK, maybe customerID, so retrieving the row must be lightning fast if you are using a modern RDBMS. Also the child tables must have a PK, and a FK pointing to the PK of parent table. That FK is also indexed. That's like the very basic task a RDBMS is made for.

Regarding the network traffic between the app server (or client app) and the DB server involved, that can also be optimized.

The point is:

Hardware and transport issues can be solved by trowing money at then.

Complexity problems derived from writing code to do things the RDBMS does more efficiently, cannot be solved by throwing money at them.

Hardware is cheap, programmers are expensive.