Legacy search: 50 seconds to under 4

In the project that I was working on at the time, the list view most of the application was built around, took 17 to 50 seconds and timed out on larger page sizes. The original code was not written by us and the project was handed over and multiple queries were using the same structure, so re-structuring or refactoring everything or even building a new structure for this specific use case, required massive changes both in Frontend and Backend, a luxury that we didn’t have at the time. Plus the performance had degraded slowly over two years as the data grew, something that we didn’t have in the beginning due to smaller size of dataset. Unfortunately the project was only in maintenance mode so big changes, or sprints were not planned at all.

Users could filter on any field. The data models and their related objects were six levels deep. So we had to have something that was dynamic and flexible.

What we did was that a custom deserializer turned their JSON filters into dynamic LEFT JOINs and returned full entities. Using DTOs would have meant rewriting the filter engine, plus changing the FE data model and requests. I tried Projections and they didn’t add any meaningful speed gain, and entity graphs wouldn’t have helped since our custom query builder already was taking care of the nested fetching.

It wasn’t N+1, even though everyone suspected it to be. SQL logs showed one large query. Every relationship was EAGER, so each row pulled its entire six-level graph, and circular references made Jackson serialize the same data repeatedly.

I wrote up five options for the PO, from targeted column fetching to a full redesign. We made a compromise and chose the most extensive one we could afford: EAGER to LAZY at every level, explicit breaks in circular references, new indexes, and some query-builder-optimizations along the way.

Switching to LAZY broke some endpoints with NonUniqueResultException. Records that were one-to-one in java code had duplicates in the database, and nothing enforced the rule at the DB level unfortunately. The properties having FetchType.EAGER somehow masked it so it went unnoticed for a long time.

The app had been creating a second child record instead of updating the first. I cleaned them up and added unique constraints. Of course this cleanup came as a form of merge, rather than simply deleting one entry. But what I found out was that in all cases, the first entry was almost always empty in terms of properties and sub-properties, I would have wanted to dig deeper on why and how this happened and the reason for this behavior but the performance improvement should have been deployed ASAP.

In the end, I don’t think the FetchType.EAGER -> LAZY had the effect that I assumed, since the query that was built later on, still fetched all properties and sub-objects eagerly and almost the same data size as before. I believe the @JsonIgnoreProperties, removing the duplicates, and small optimizations that I did to the query builder along the way made a bigger difference.

I measured the total response time only, before and after. It would definitely make much more sense to measure each improvement in isolation separately and then draw a conclusion on which part did the main improvement.