Using projections in your data-access layer

If you care about your data-access layer to make it as fast as possible, you should consider using projections for reading data. Check a simple example together. We have cities where certain people live in. This is how one can model this with JPA.

 

Let’s say we want figure out the all the spoken languages of the people living in a city. One can come up with the following solution:

This code will result in the following query:

If you run this with Oracle, you will get the following execution plan:

As you can see, Hibernate will issue a query to fetch all – except lazy – attributes from the database despite the fact that you will only use the language attribute of the resulting entities. Of course this is not the most effective way as you make unnecessary mandatory work for your database.

If you fetch only the data you need, you will speed up your application as well as your database. Imagine if you only need the language, why not fetch the language attribute only? For sure, you can do this, those selections which fetch only a subset of the data are called projections. With JPA, Spring Data, you can easily create projection objects as I will show it in the next couple of minutes.

Benefits

There are 3 main benefits of using projections – if you use them properly:

  • Hibernate doesn’t have to manage entity state transitions. In case you fetch hell lot of data, you will end up with a slow application just because Hibernate has work to do. If you use projections, you can eliminate this cost.
  • Memory footprint. Let’s say the query will result in lot of data. If you fetch the entities itself, the database have to fetch all the data from the disk, has to send it back to your application, your application has to store it in the memory. It’s a waste, isn’t it?
  • Covering index. It’s not any kind of magic, basically the idea here is that you fetch the data which you have index on. In this case, the database can skip the whole table access and go with faster index access.

 

Defining projections

Okay, now let’s see how can we define projections within JPA with JPQL or Criteria API and with Spring Data JPA.

What you need for a JPA projection is a simple POJO with a proper constructor.

Then in JPQL, you can easily write the following query:

Important note that you have to use fully qualified class name as the JPA provider doesn’t know anything about data holder classes other than entities.

In Criteria API, you can go with the following implementation:

Both implementations will result in the following SQL queries:

The execution plan will look like the following:

If you compare the memory consumption to the first approach, it’s almost half in size. Of course it’s not that big of a deal now as we operate on really small tables but if you think about dozens of attributes unnecessarily fetched, it would be more visible. If you don’t believe me, check out Lukas Eder’s article.

It’s even easier to create a projection with Spring Data. You only need to create an interface which represents the projection, in this case:

The Repository looks like this:

It’s really really easy to do this with Spring Data. If you call this repository method, it will execute the following query:

As you can see, Spring Data generates a little less efficient query as it joins the whole city table despite the fact it’s not necessary. I’ve filed a ticket to take a look on this, maybe I’m missing something.

Conclusion

If you want to have a fast data-access layer, there are several things you have to care about. This includes using projections as in this way you can optimize to fetch only the data you need. For reading purposes, I’d really suggest using projections instead of entities as it can boost up the performance of your application. For changing states, use entities.

The source can be found on GitHub. Feel free to reach me out in case of questions in the comments or on Twitter.

One thought on “Using projections in your data-access layer

Leave a Reply

Your email address will not be published. Required fields are marked *