Hibernate performance tuning tips

Question

I'm developing a small application, which consists of a single table.

I am using technologies are: NetBeans 8.1 Java 8 Hibernate 4.3.x Informix Primefaces 5

I had to investigate a time to connect with Informix Hibernate, but I got it, and the application displays the list with the requested data correctly.

The problem arises with the performance of Hibernate, which is very poor, especially considering that the table contains only 36000 records.

On each page change takes about 6 or 7 seconds.

I have been researching in the official documentation of Hibernate, but can not find concrete examples to improve performance.

Herewith the application code:

hibernate.cfg.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
   <property name="hibernate.dialect">org.hibernate.dialect.InformixDialect</property>
   <property name="hibernate.connection.driver_class">com.informix.jdbc.IfxDriver</property>
   <property name="hibernate.connection.url">jdbc:informix-sqli://127.0.0.1:1526/chicho:INFORMIXSERVER=ol_chicho</property>
   <!--<property name="hibernate.connection.datasource">jdbc/votacion</property>-->
   <property name="hibernate.connection.username">informix</property>
   <property name="hibernate.connection.password">informix</property>
   <property name="hibernate.connection.autocommit">true</property>
   <property name="hibernate.current_session_context_class">thread</property>
   <property name="hibernate.default_schema">informix</property>
   <property name="hibernate.show_sql">true</property>
   <property name="hibernate.hbm2ddl.auto">validate</property>
   <property name="hibernate.cache.use_second_level_cache">true</property>
   <mapping resource="pojos/Xxpuedenvotar1.hbm.xml"/>
 </session-factory>

Pojo:

package pojos;
// Generated 23/08/2016 22:07:42 by Hibernate Tools 4.3.1



/**
 * Xxpuedenvotar1 generated by hbm2java
 */
public class Xxpuedenvotar1  implements java.io.Serializable {


 private Integer nroaccionista;
 private Short estado;
 private Integer idcliente;
 private String razonSocial;
 private Short idlocalidad;
 private Short zona;
 private String calle;
 private String puerta;
 private String localidad;

public Xxpuedenvotar1() {
}


public Xxpuedenvotar1(Integer nroaccionista) {
    this.nroaccionista = nroaccionista;
}
public Xxpuedenvotar1(Integer nroaccionista, Short estado, Integer idcliente, String razonSocial, Short idlocalidad, Short zona, String calle, String puerta, String localidad) {
   this.nroaccionista = nroaccionista;
   this.estado = estado;
   this.idcliente = idcliente;
   this.razonSocial = razonSocial;
   this.idlocalidad = idlocalidad;
   this.zona = zona;
   this.calle = calle;
   this.puerta = puerta;
   this.localidad = localidad;
}

public Integer getNroaccionista() {
    return this.nroaccionista;
}

public void setNroaccionista(Integer nroaccionista) {
    this.nroaccionista = nroaccionista;
}
public Short getEstado() {
    return this.estado;
}

public void setEstado(Short estado) {
    this.estado = estado;
}
public Integer getIdcliente() {
    return this.idcliente;
}

public void setIdcliente(Integer idcliente) {
    this.idcliente = idcliente;
}
public String getRazonSocial() {
    return this.razonSocial;
}

public void setRazonSocial(String razonSocial) {
    this.razonSocial = razonSocial;
}
public Short getIdlocalidad() {
    return this.idlocalidad;
}

public void setIdlocalidad(Short idlocalidad) {
    this.idlocalidad = idlocalidad;
}
public Short getZona() {
    return this.zona;
}

public void setZona(Short zona) {
    this.zona = zona;
}
public String getCalle() {
    return this.calle;
}

public void setCalle(String calle) {
    this.calle = calle;
}
public String getPuerta() {
    return this.puerta;
}

public void setPuerta(String puerta) {
    this.puerta = puerta;
}
public String getLocalidad() {
    return this.localidad;
}

public void setLocalidad(String localidad) {
    this.localidad = localidad;
}
}

DAO:

package Dao;

import Interfaces.InterfazSocios;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import pojos.Xxpuedenvotar1;

/**
 *
 * @author Gustavo
*/
public class DaoSocios implements InterfazSocios {

private List<Xxpuedenvotar1> listaSocios;

@Override
public List<Xxpuedenvotar1> verTodos(Session sesion) throws Exception {
    String hql = "FROM Xxpuedenvotar1 ORDER BY NroAccionista";
    //Query consulta = sesion.createQuery(hql).setCacheable(true);
    this.listaSocios = sesion.createCriteria(Xxpuedenvotar1.class).list();
    //this.listaSocios = consulta.list();
    return this.listaSocios;
}

}

I think with these files is sufficient for analysis, since the application works well, with the exception of its slowness.

Thank in advance for your kind attention.

Answer 1

First of all, there are many things you can do to speed up Hibernate. Check out these High-Performance Hibernate Tips, or this High-Performance Hibernate video presentation.

Now, back to your question. You are using the DriverManagerConnectionProvider which only provides a rudimentary connection pooling solution. Better use HikariCP since it's the fastest one, and it's also available via the hibernate-hikaricp Maven dependency.

Related to your statement:

The problem arises with the performance of Hibernate, which is very poor, especially considering that the table contains only 36000 records.

On each page change takes about 6 or 7 seconds.

Why would you want to fetch 36k records in a single shot?

You can't display them into a UI? That's why we have data pagination after all.

Even for a batch processor, you are better off splitting the whole workload into multiple smaller data sets that allow you to avoid long-running transactions, and even split the load among multiple worker threads.

Answer 2

Can try any of these or all of these depending on the configuration, availability of modifying the configuration properties.

• hibernate.show_sql mark this to false, once you are done with your analysis of query formation. This takes time to log.
• You can change connection pool size configuration, if you are using one

  <property name="hibernate.c3p0.min_size">5</property>
  <property name="hibernate.c3p0.max_size">20</property>

This helps in attaining the connection quickly from the pool. Avoids time to get the connection.

• If you have huge data, Please mark the respective column in JPA class with @OrderBy.

• Hibernate search provides @SortableField to annotate the field, This indexes internally and helps in retrieving the sorted results.

[EDIT]

If Integer nroaccionista is primary key, it is indexed by default. Mark the column in JPA class with @Id and the DDL creation statement generated with create the index by default. Indexed column helps in better search and sort results.

Hope this helps.