At work I was assigned a task to investigate on the severe slowness of an application I am not familiar with. Simple requests were taking several minutes to resolve.

Here's how it was before (simplifying):

  • There is a facade method to get a subset of information about orders, with the customers

  • There is a CustomerDAO, built around CRUD principles

  • There is a OrderDAO, built around CRUD principles

  • The facade method calls OrderDAO.find(...), then for each order, calls CustomerDAO.find(order.getCustomerId())

I'm oversimplifying here, I'll pass most of the details. This isn't even orders and customers I had to deal with, and there are other DAOs called in the process. You have to trust me when I say that the matter was much more complicated than this, the amount of redundant calls was absurd.

There was a case where a findBy(...) method returned all the parents and each parent had a list of children, but for some reason within the DAO logic it would call find(parentId) for the child, so the parent's model was present 1 + the number of children times in the returned model, totaling 1 + the number of children x 2 queries per parent. I don't know why the children have a copy of their parent, but it's done that way; enhancing that would be out of the scope of my activities.

More importantly, the facade only returns a subset of the retrieved information present in the model. Some data that had their own query within the DAO logic was discarded.

At first I tried to preserve the current architecture by adding a cache and optimizing some redundant calls, but the results were still mediocre.

I have solved the slowness issue by performing a database query joining the tables and building the subset models instead of building the complete models, reducing the processing time from several minutes down to a couple seconds. The facade signature remains unchanged.

However I feel like this solution is completely out of place in regards to the rest of the application. The logic happening within that new method is very specific. In addition, I am no longer returning Customers or Orders, so this new strategy does not seem to belong in either DAOs.

What would be a correct approach of dealing with a performance issue that happens to be easily solvable by bypassing CRUD oriented DAOs? Typically if I have multiple CRUD oriented DAOs but I need some kind of large data join, what am I supposed to do?

  • Sounds like an N+1 problem to me. What prevents you from doing exactly what you propose, which is to bypass the CRUD oriented DTOs or short them up with custom queries? Dec 31 '16 at 13:29
  • 1
    A DAO layer should abstract away the access to a persistant system. That means that a DAO's interface should be closer to your application domain, not closer to your persistance system. What you have here is a badly designed DAO layer. "Built around CRUD principles" screams "RDBMS". The solution is to relax a bit the "CRUD only" contract and add other methods (like mentioned by Doc Brown in his answer) that retrieve agregated data with SQL joins.
    – Bogdan
    Dec 31 '16 at 14:30

Without knowing the real code I can only guess around what is actually missing here, but to my experience, lots of performance issues in DAOs can be solved by adding some simple extensions to standard CRUD methods.

For example,

  • the Read or SELECT methods can provide a string parameter wherecondition, and return multiple records at once. This approach will allow the caller to make use of complex joins, subqueries, or UNION operations, in a transparent manner, in conjunction with standard CRUD operations. (The drawback is, this introduces a possibility of placing vendor specific SQL outside of the DAO layer). Sometimes it is also useful to provide a parameter with a list of IDs / keys into the Read method, and the latter makes use of the SQL command ".. WHERE id IN(...)" to load those records in one roundtrip. This allows working with whole sets of records without running into the problem with vendor specific SQL.

  • introducing the possibility of "impartial loaded objects/records": create methods in the DAO layer where one can specify not to load the full objects immediately. Provide a mechanism to allow the loading of the missing parts of objects/records afterwards, but only if they are really needed ("lazy loading"). This can become very efficient when the lazy loaded parts would require additional queries or complex subqueries.

So I think you get the general idea: instead of bypassing the CRUD layer, introduce some optimization options into the CRUD layer.

If the CRUD code is not hand-written, but generated, it is perfectly possible to the extend the generator and let it add the suggested optimization options to all the DAOs of your data model.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.