CODEWITHSUNDEEP
javasql-serverperformancehibernatejpa
goldaniga
goldaniga

Reputation: 113

JPA/Hibernate performance

My Java EE application works with rather large relational DB of two tables app. of 3.5 mln rows each. The two tables are named, say, 'User' and 'Device' - one to many with FK on 'Device' table. I need to extract devices for user. The query is pretty simple:

SELECT * FROM Device d WHERE d.user_id = 'some_id'

I am using SQL Server 2012, and this query needs less than 1 ms to run. But when I use Criteria API or JPQL or Native JPA Query it needs more than 1 second!

If I store a Collection in a Users's class and fetch the devices lazily, it needs over 200 ms.

I am using Hibernate.

Maybe there is a lack of information to answer my question, if so, please, say what information should be provided by me.

So, what may be the reason of such low performance and what are the main patterns when working with large data sets (3.5 mln, as I mentioned) with JPA?

Sry for poor English.

upd. The code, that needs 200 ms

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;
    }
}

Upvotes: 3

Views: 2679

Answers (2)

goldaniga
goldaniga

Reputation: 113

The problem was that Java sends parameters of queries in Unicode, and SQLServer has to transalte all the columns to Unicode too. Setting the sendStringParametersAsUnicode=false parameter to connection in persistence.xml will help. The piece of code, that costed 200 ms, now costs only 2-5 ms. Unfortunately, I spent 2 days to find the reason, 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

Upvotes: 0

Tobb
Tobb

Reputation: 12180

When you run a JPQL query, the result itself is not necessarily the only thing that is fetched from the database (you don't necessarily query for a single).

If your Device class has a reference to a User(or many), the User will also be loaded when you query for the Device. Also, there is some overhead associated with instantiating objects and registering them in the EntityManager(although I suspect that this overhead is not that large.)

In essence, you can't expect a JPQL query to run as fast as a SQL query, since they are far from the same, even though they are syntactically similar.

If you write a native sql query and run it through Hibernate I would expect it to take about as much time as running a sql query directly.

When it comes to lazy loading, Hibernate needs to revisit the database in order to retrieve the lazily loaded entities, which of course will take some time. You save time loading the owning object, but the time saved is spent once you try to load the lazy-fetched association (although, from what I've seen, eager fetch takes about as much time as lazy fetch and then lazy load, only a few ms separate 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

Upvotes: 1

Related Questions