Vehicle listing optimization (Spring data JPA)

Fleetx has come a long way in serving fleet operators and enterprises with a rich set of features along with our highly accurate real-time vehicle tracking as well as vehicle listing. Given that, our primary focus is to keep the performance of our core products up to the mark. A customer should always be able to view the analytics of his vehicles overall along with complex filters of groups or tags.

Recently, we faced the challenge of our vehicle listing API getting pretty slow for our bigger clients with more than 2k vehicles installed. Although, in the recent past, we had optimized our vehicle listing API by creating very specific entities with only those fields that are required in the response (We use JpaSpecificationExecutor and create Criteria queries based on dynamic filter parameters).

Still, our listing API was taking ~15sec for fetching ~2.5k vehicles. It was a huge performance bottleneck.

Behind the scenes

Given that we were creating queries on-demand using criteria builder, queries were bound to be a little slower than a named query or native query. But, such a performance drop was not at all acceptable. So, we enabled hibernate debug logs to see what’s really going on:-

logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
logging.level.org.hibernate.SQL=DEBUG

What we saw was that hibernate was issuing queries (n+1 queries) for each and every vehicle for all @ManyToOne or @OneToOne associations. Those associations were marked as FetchType EAGER. Apart from that, hibernate was issuing tons of queries on @OneToMany or @ManyToMany associations in our VehicleListing entity.

Optimization #1

To prevent n+1 query problem, first of all, I overrode the findAll method with specification and pageable as parameters as below :

@Override
@EntityGraph(attributePaths = {“vehicleModel”, “group”, “vehicleType”, “device”}) // etc..
Page<VehicleListing> findAll(Specification<VehicleListing> spec, Pageable pageable);

As you can see, on the overridden method, I applied EntityGraph to eagerly fetch all the attributes of @OneToOne or @ManyToOne as well as marked them LAZY (Eager fetch is a code smell).

Performance improved drastically as instead of so many additional queries, data is getting fetched in a single DB call.

Optimization #2

There was a @OneToMany association that was being fetched, filtered on a particular key in the application and a single instance of the relationship was being returned in my DTO object. Why not instead apply the filter at the DB level? So, I wrote a query with the filter key which returned a DTO projection to just return what I want. Calling this single additional query separately fetching associations for all vehicles at once improved the performance further.

Optimization #3

Finally, there was a @ManyToMany association which was triggering so many additional calls for each and every vehicle. Then comes to the rescue, @BatchSize annotation of hibernate. I specified a suitable batchSize for my collection (eg. 100). Immense performance benefit was observed after this change.

Optimization #4

One minor optimization was to apply @Transactional(readOnly = true) on my service method to ensure all read queries happen in a single transaction thus preventing multiple transactions for each read query.

Finally, when I tested the API response time again, it responded in ~450ms for vehicles size of ~2.5k

That was an epic performance improvement.

Thank you and happy learning !!