JPA / Hibernate performance
A Java EE application works with a fairly large relational database of two tabular applications. 3.5 million rows. The two tables are named, for example, "User" and "Device" - one for many with FK in the "Device" table. I need to retrieve devices for a user. The request is pretty simple:
SELECT * FROM Device d WHERE d.user_id = 'some_id'
I am using SQL Server 2012 and this query takes less than 1ms. But when I use the Criteria API or JPQL or JPA native query it takes over 1 second!
If I store a collection in the Users class and lazily grab to devices, it takes over 200ms.
I am using Hibernate.
There may be a lack of information to answer my question, if so please tell me what information I should provide.
So, what could be causing such poor performance and what are the main patterns when working with large datasets (3.5 million as I mentioned) with JPA?
Too bad english.
obn. Code that takes 200ms
Collection<Device> userDevices = user.getDevices();
for (Device device : userDevices) {
if (device.getActive() && device.isToken())
sender.sendSms(user.getMobilePhone(), text); // costs 0, i am using a stub
}
And the user class:
@Entity
public class User {
@Id
private String userId;
private String name;
//bla bla bla
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "user")
private Collection<Device> devices;
//bla bla bla
public Collection<Device> getDevices() {
return devices;
}
}
source to share
When you run a JPQL query, the result itself is not necessarily the only thing being retrieved from the database (you are not necessarily asking for any).
If your class Device
has a reference to User
(or many) User
will also be loaded when requested for Device
. In addition, there is some overhead associated with creating objects and registering them in EntityManager
(although I suspect this overhead is not that big).
Basically, you cannot expect a JPQL query to execute as fast as a SQL query, since they are far from being syntactically similar.
If you write your own SQL query and run it through Hibernate, I would expect it to take about the same amount of time as running the sql query directly.
When it comes to lazy loading, Hibernate needs to revisit the database to get lazy loaded objects, which of course will take a while. You save time loading the owner object, but the time taken to save will be wasted as soon as you try to load the lazy association (although, from what I've seen, the expected fetch takes about the same amount of time as lazy selection and lazy loading , only a few ms separates the two.)
If you want to see the sql that Hibernate generates based on your JPQL query, add <property name="hibernate.show_sql">true</property>
to your Hibernate config and the following to your log4j.properties:
log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE
source to share
The problem was that Java sends query parameters in Unicode and SQLServer has to translate all columns to Unicode. Setting sendStringParametersAsUnicode = false for connection in persistence.xml will help. A piece of code that costs 200ms now only costs 2-5ms. Unfortunately I took 2 days to find the cause, as I thought the problem was in Hibernate. See: http://www.jochenhebbrecht.be/site/2014-05-01/java/fixing-slow-queries-running-sql-server-using-jpa-hibernate-and-jtds
source to share