Getting the column value using Entity getter vs custom SELECT to prevent fetching of additional columns

Question

This question might be little subjective. Suppose I have a Post entity which has User association which it. This User is the owner of the entity. Now I want to get the owner id(just the Id), which is the efficient way to do this?

1. post.getUser().getId()
2. SELECT u.userId from Post p JOIN User u ON p.user.email = u.email where p.id = 1

I find the 2nd to be more efficient since we are not bringing extra columns and getting only the required field, so less bandwidth consumed.

These will be executed under Spring-Data-Jpa @Transactional

Answer 1

TL;DR: It depends

Efficiency is not subjective, but it is a question of definition and context.

You need to consider what efficiency really is. Do you only consider the efficiency at runtime? Or is the efficiency at development time relevant? Is efficiency for you the same as performance/speed? Or is the load on the database server relevant?

In most cases for most projects the development time is actually more relevant. Most code is not on a hot execution path, so as long as the performance is not horrible it doesn't matter that much.

But we typically only talk about the cases where runtime efficiency actually does matter, either because our servers get under heavy load or because response times aren't satisfying.

So once you know what you actually want and need you can start measuring/estimating the two approaches. Again how much effort you put in these depends on how much you might gain from an improvement.

When measuring make sure you measure in realistic environments, this includes that other queries that get typically get executed in the same or parallel threads not to be considered as well.

With all that said:

On their own, the dedicated select statement will be faster and consume less resources on the db server, network and on your application server for the following reasons:

• less data has to be selected, with a good chance that data can be loaded from indexes instead of accessing tables, which will probably necessary for materializing a two full objects.
• less data has to be transferred over the wire.
• Instead of constructing two full objects only one primitive has to get constructed. Since in setting each attribute there is possibly a conversion and creation of intermediate objects involved, this can be a significant difference.

But in an actual application it is perfectly possible that accessing the post and the user will be satisfied from the 1st level cache, i.e. the objects are already loaded into the EntityManager and are already fully materialised. In this case the dedicated SQL statement will be much less efficient by all reasonable metrics.

Answer 2

You can use the SELECT NEW construct in JPQL, which instantiates an object with just the column value(s) you need. See Create new object in SELECT statement

Answer 3

If we compare this scenario with two parameters: performance and development efforts, then Criteria API queries are more efficient. because...

Fetching all columns from the self table doesn't make big difference, but fetching using foreign keys from different table despite join fetch mode or select fetch mode clearly affects performance.

0. Decreases boilerplate code
1. we can write automation for commonly used business logic i.e pagination, orders etc, for that we can use annotations on @Entity POJOs.

Also, thing to care about for ORM best practices: Use FetchType.LAZY wherever it possible, fetching associated entities hits extra queries that will clearly decrease performance (you can observe by generating user traffic). Use @DynamicUpdate and @DynamicInsert for efficient update, insert queries.