Hibernate is so slow on remoted database (Postgresql)

Question

I'm using hibernate but I'm facing a performance problem with it!
When I use it on a local database it's extremely fast but very slow on a remoted database (and by remoted database I mean in a local network)!!
Why?!!

I know you will say maybe it's a lazy loading problem but I have tested it on simple select for one entity (without children) but still slow, check this out:

public List<Employee> read() {
        List<Employee> objects = null;
        Transaction tx = null;
        try {
            tx = HibernateUtil.SESSION.beginTransaction();
            Long start = System.currentTimeMillis();
            objects = HibernateUtil.SESSION.createQuery("FROM Employee AS T ORDER BY T.id DESC").list();
            Long stop = System.currentTimeMillis();
            System.out.println( "Time: " + (stop - start) + "ms." );
            tx.commit();
        } catch (HibernateException e) {
            if (tx != null)
                tx.rollback();
            e.printStackTrace();
        } 
        return objects;
    }

As you can see a simple select query and this is the result I get:

Locally: 31ms
Remotely: 356ms !!!!

And this is my config file:

<!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.PostgreSQLDialect</property>
        <property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
        <property name="hibernate.connection.username">postgres</property>
        <property name="hibernate.connection.password">root</property>
        <property name="hibernate.connection.url">jdbc:postgresql://192.168.1.3:5432/my_database</property>
        <property name="connection_pool_size">1</property>
        <property name="hbm2ddl.auto">update</property>
        <property name="show_sql">false</property>
        <property name="hibernate.enable_lazy_load_no_trans">true</property>


    </session-factory>
</hibernate-configuration>

Can you explain this ?

It is possible that it is a network delay, have a look at your ping to database server. — dbf, Sep 19 '16 at 16:03
make show_sql true and dump SQLs for a given operation, and try to execute the same queries (read only queries) using a client tool you can cheek whether it is problem related to the network. straight forward way to profile the application with profiler enabled JPA/JDBC (ex: JProfiler). if the target db server is running in a VM (ex VMware), this can be problem in that VM configuration. — hunter, Sep 19 '16 at 16:35
Possible duplicate of [poor Hibernate select performance comparing to running directly - how debug?](http://stackoverflow.com/questions/13306548/poor-hibernate-select-performance-comparing-to-running-directly-how-debug) — Rohit Gaikwad, Sep 19 '16 at 17:37
@RohitGaikwad Not the same man he has problem in his query and he fixed it, but me I just used a simple select query and it's slow. — SlimenTN, Sep 20 '16 at 07:02

score 0 · Accepted Answer · edited Jan 06 '18 at 10:41

0

You need configure a conection pool

Reasons: The anatomy of Connection Pooling

For example, you can use C3P0 with Postgresql:

Regards

edited Jan 06 '18 at 10:41

Vlad Mihalcea

142,745
71
566
911

answered Sep 19 '16 at 16:28

esmoreno

658
5
12

1

Thank you for your answer, this was a door to learn more about connection pool which is very useful to learn that gives you a better database connection experience. This solved the half of the problem (and the most important one ) the second half is to optimize my queries. Thnx again :) – SlimenTN Sep 20 '16 at 10:53

Hibernate is so slow on remoted database (Postgresql)

1 Answers1