Spring, JPA and Hibernate multiple queries executed from one sql statement

Question

I'm completely new to Spring, JPA and Hibernate, so apologies if I do not phase this question correctly.

I have been given an application using these frameworks that has multiple performance issues.

I have done numerous searches to no avail - probably due to my inability to phrase my question correctly.

The scenario is (in a nutshell) that when one sql statement is executed via the EntityManager, it in turn invokes a related query per row of the result set. In my case the one sql statement could cause hundreds (and in some cases thousands) of related sql statements to be invoked - killing our server.

A contrived example, could be:

public class Job
{
    public long jobId;
    public String name;
    public List<Transaction> transactions;
} 
public class Transaction
{
    public long transactionId
    public List<Stock> stocks;
}
public class Stock
{
    public long stockId;
    public String name; 
}

The code uses a statement like:
String jpaQuery = "select distinct j from Job j where j.jobId = :jobId order by name asc";
Query query = entityManager.createQuery(jpaQuery);
//set parameters...

//This will return a List<Job>, containing lists of Transaction and Stock objects 
return query.getResultList();

The execution of this single sql statement results in multiple sql statements being executed (which can be viewed in the tomcat log / eclipse console), so they are being invoked by the code/framework. EntityManager has not been extended, so the framework(s) are doing the work.

Now my question - thanks for reading this far...

Within Spring, JPA and Hibernate, what would be the best way to approach this so one call to the database returns the required objects without multiple requests to the database?

My thought of an approach is to have the single sql statement to invoke a stored procedure, that could either return multiple result sets or just one result set containing data for all related objects and have the framework do the rest (instantiate the relevant objects).

What I have no idea on is how to approach this within the Spring / JPA / Hibernate environment. Could someone please point me to resources that would give me examples/ideas on how to approach this? Or keywords to search for?

Version: Spring: 3.0.6.Release Hibernate: 3.5.0-Beta-2

Thanks very much Steve

You may be able to reduce this to three queries by using Hibernates Second-Level Cache (baeldung.com/hibernate-second-level-cache). You would first query for Stocks, than query for Transactions, and then query for Jobs. This would be dependent on your ability to create a single query to obtain all the required Stocks and a single query to obtain all the required Transactions. The second level cache would prevent the individual querying for each Stock and Transaction, as Hibernate would have those objects already cached and would not need to invoke a query. — JohnC
– JohnC, Commented Sep 22, 2017 at 15:15

dunni · Accepted Answer · 2015-04-11 06:05:07Z

1

First, this is not an SQL statement, it is a JPQL statement, which is a huge difference (not syntax wise, but logic wise).
Second, the reason for the multiple SQL statements is the one-to-many relationshop from job to transaction to stock (it's called N+1 problem). Depending on your use case you could change the relationships to lazy loading. This means, they are only loaded when you need the objects. However, this can cause other problems, if it is not handled correctly. You could also try to use Join Fetching. This will create a single query with a join, instead if issueing one subselect per element in the collection.

answered Apr 11, 2015 at 6:05

dunni

44.7k11 gold badges108 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TCBOOne Over a year ago

Thanks dunni for the quick response and clarification. All of the objects are required to be loaded at the one time. I will check out the links, certainly join fetch looks like the way to go, once I get my head around the syntax.

Roland Over a year ago

I have a similar issue. All foreign rows (I use @OneToOne and @OneToMany on several places) are being distinctly (each separately) loaded by a SQL query with a WHERE statement. I wonder how I can get rid of that WHERE statement? I know FetchMode.LAZY but that will only delay the problem until your user base grows. I currently use the default (EAGER). Any way to tell the JPA (only javax.persistence.* used here) to fetch whole table? RAM is not an issue but performance can be.

Collectives™ on Stack Overflow

Spring, JPA and Hibernate multiple queries executed from one sql statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related