Hibernate taking too long to execute simple queries

Question

I'm working on a old java source code, which is using by defauly jdbc preparedstatement to make database queries. It's a big project and to make it more scalable, I've decided to refactor existing code to support hibernate library.

I'm using a factory to store my entity manager factory and repository pattern to keep my hibernate queries. Problem is that simple queries are taking like 3-4 seconds to execute, when it should be executed in ms.

For example, I have a table which contains 30-40 rows. I'm doing a query in this table and it's taking like 3-4 seconds to execute. If I execute the same query generated by hibernate (I got it from hibernate logging) it takes a few milliseconds. I've tried to query data in other tables and the result looks the same.

What I've tried so far?

Add constructor: Checking some stack overflow posts and google I found an information that hibernate by default use the default constructor and it might take some time to loop through all records and instantiate them properly this way. So, in my query, I've tried to add the following to my query:

em.createQuery("SELECT new Player(...) FROM Player p").getResultList();

Unfortunately, the result looks the same.

Query after application stands up for a while: This query above is one of the first lines of code executed when my application is starting, so after searching more stuff, I found some information that it could be because it's the first query being executed and it could take some time until hibernate "get ready" to be used properly. After this, I tried doing another query (in another table) & the same query in another completely different proccess of my application and the result looks the same. And again the result query being displayed at hibernate output log executes much faster if directly executed on my sgdb.

I'm pretty sure it's the query taking long to be executed, not any other process until the query, because I debugged the whole code and it execution looks pretty fine & fast until the query point.

Other information: Not sure if useful but code is being compiled in JDK 1.8 and hibernate version looks to be 5.4.

Any suggestions?

Edit: Here at the classes being used to retrieve information

Player class:

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="characters")
public class Player implements Serializable {

@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE)
@Column(name="id")
private int id;

@Column(name="gm")
private int gm;

@Column(name="name")
private String name;

@Column(name="loggedin")
private int loggedin;

public Player() {}

public Player(int id, int gm, String name, int loggedin) {
    this.id = id;
    this.gm = gm;
    this.name = name;
    this.loggedin = loggedin;
}

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public int getGm() {
    return gm;
}

public void setGm(int gm) {
    this.gm = gm;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public int getLoggedin() {
    return loggedin;
}

public void setLoggedin(int loggedin) {
    this.loggedin = loggedin;
}
}

Repository with the query:

public class PlayerRepositoryImpl extends GenericRepositoryImpl<Player> implements PlayerRepository {

@Override
public List<Player> getAllStaffMembers() throws DatabaseException {
    try {
        return em.createQuery("SELECT new Player(p.id, p.gm, p.name, p.loggedin) FROM Player p WHERE gm >= :level")
            .setParameter("level", PlayerGMLevelEnum.GM.getLevel())
            .getResultList();
    } catch(Exception ex) {
        ex.printStackTrace();
        throw new DatabaseException("Failed to retrieve data from database.");
    }
}

}

PlayerGMLevelEnum.GM.getLevel() returns a simple int value which is 3.

Edit²: I just got back from work and started to debug a lil bit more and I realized the problem isn't the query at all. The problem is the database connection is taking a while to be full run. After the connection is make, the query executes real fast (probably same speed as sgdb).

Here's my persistence.xml file:

  <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
  <property name="hibernate.hbm2ddl.auto" value="update"/>
  <property name="hibernate.show_sql" value="true" />
  <property name="hibernate.generate_statistics" value="false" />
  <property name="hibernate.format_sql" value="false" />
  <property name="hibernate.temp.use_jdbc_metadata_default" value="false"/>

Any suggestions? Did I miss some configuration?

Edit³: I don't think there's a bug or any configuration missing just the connection takes a while to be done anyways.

My new thought is how can I cache this database connection using SessionFactory? Is it safe to keep a database connection cached for minutes? I'm coding an application that needs constant use of database, so I need to be able to execute queries fast as possible. I was thinking to cache my connection for like 5-10 minutes, is that a terrible idea? Is there any other option?

Edit4: I've found my solution, I'm just not sure if it's the best possible. I created a singleton class to keep my SessionFactory and plan to use it for the whole application life time. Is it a great idea?

Did you try to profile your application to figure out where the time is spend? — second
– second, Commented Sep 11, 2019 at 20:08
No, I didn't. I've not much experience in profiling. Your suggestion is that through profling I could be able to see if any other process is being executed while the query is running? Because I'm pretty sure it's the query causing delay. If so, do you have any to indicate in this kind of situation? — gustavo.lei
– gustavo.lei, Commented Sep 11, 2019 at 20:22
1) Show us the source of your class Player. 2) Show us the complete query. — mentallurg
– mentallurg, Commented Sep 11, 2019 at 20:49
Does your object have relations? maybe the query you saw is just one query and hibernate performs some others that you missed? Maybe you have some logic in the entity class? Anyway @mentallurg is right, just post the Player class and the query — paranoidAndroid
– paranoidAndroid, Commented Sep 11, 2019 at 21:14
@paranoidAndroid: Exactly :) If relations are defined badly, may be 1000000 records are loaded via this single query. — mentallurg
– mentallurg, Commented Sep 11, 2019 at 21:16

gustavo.lei · Accepted Answer · 2019-09-12 12:12:46Z

Thank you for everyone responses. In the end, I was able to find out the issue here. Let me share a bit, what I've learned:

My problem wasn't related to the query itself. Query was executing pretty fast, just like when executing at my sgdb.
I was initializing a new SessionFactory before every database connection. SessionFactory are heavy weight objects and they take some time to be initialized properly.
SessionFactory objects should be one for DataSource of your application (one by database, basically (?)). It is thread safe, which means you can keep it one for application that it'll work fine.
Sessions are how you initialize a database connection itself (similar to Connection object). Sessions are not thread safe, so it should be created before any database interaction.
To keep your SessionFactory object, you can use a Singleton pattern, just like this: https://www.onlinetutorialspoint.com/hibernate/singleton-hibernate-sessionfactory-example.html

When you need to make a database query, you initialize a new Session and do your query, something like this:

session = getSession();
Transaction t = session.beginTransaction();
List<Player> result = session.createQuery("SELECT new Player(p.id, p.gm, p.name, p.loggedin) FROM Player p WHERE gm >= :level")
     .setParameter("level", PlayerGMLevelEnum.GM.getLevel())
     .getResultList();
 t.commit();
 session.close();

My getSession() method just initialize a session from SessionFactory singleton.

If anything's wrong, let me know so I update and make it as it should be.

I hope this can be useful to someone else, thank you for all the support.

Collectives™ on Stack Overflow

Hibernate taking too long to execute simple queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related