Hibernate, Query SQL with params. Bad Performance

Question

I have an issue related to the performance of a SQL query using JPA.

Response time:

Using Toad - 200 ms
Inside my project using Glassfish 2.1, Java 1.5, Hibernate 3.4.0.ga - 27 s

Oracle 10g

Glassfish and Toad are hosting in the same machine. I have connected to other ddbb from the same Glassfish, JPA, etc, and performance is good. so I don't know what is happening.

I have two different environments. In one of this (the worst, theoretically) it runs fast. In the other, it's where I have the problem.

The query is executed with a Javax.persistence.Query object and in this object are inserted the parameters with the method setParameter(). After that, I call to getResultList() method and this method returns the registers to me. In this point is where the time is excessive.

But, if I replace the parameters in code and I call to getResultList() method directly, without setting parameters into Query object, the performance is much better.

Anyone could help me with any clue about the problem or how to trace it?

Query

SELECT A, B, ..., DATE_FIELD FROM
     (SELECT A, B, C FROM Table1 
        WHERE REGEXP_LIKE(A, NVL(UPPER(:A),'')) AND DATE_FIELD = :DATE
        UNION
      SELECT A, B, C FROM Table2 
        WHERE REGEXP_LIKE(A, NVL(UPPER(:A),'')) AND DATE_FIELD = :DATE)

Java Code

public Query generateQuerySQL(String stringQuery, HashMap<String, Object> hParams) {
    Query query = em.createNativeQuery(stringQuery);
    if (hParams != null) {
        for (Iterator<String> paramNameList = hParams.keySet().iterator();  paramNameList.hasNext() {
            String name = paramNameList.next();
            Object value = hParams.get(name);
            query.setParameter(name, value);
           }
       }
    return query;
   }

The only possibilities is the size of data sets or query caching configuration at database. — Phani
– Phani, Commented Jan 31, 2013 at 13:21
Finally, it seems a bug of hibernate with date tyoe params. If I replace this date to String, the performance is right — Javi Pedrera
– Javi Pedrera, Commented Jan 31, 2013 at 17:10

Community · Accepted Answer · 2017-05-23 11:52:43Z

2

Query query = em.createNativeQuery(stringQuery);

will elaborate a query plan to execute the query. Unfortunally the metadata that is used to elaborate the query plan do not fit the actual parameters values that will be used when the query will be executed.

If you substitute the parameter before elaborating the plan : the plan is fine and run very fast.

1 Comment

Javi Pedrera Over a year ago

I have tested using parameters and avoiding the DATE type, and the performance is good. I think that the problem comes from the Date param. Thanks

Damith Benaragama · Accepted Answer · 2020-11-20 13:38:54Z

1

you should change cursor_sharing = FORCE in oracle to enable hibernate support in JPA for oracle.

please refer to following for more details

answered Nov 20, 2020 at 13:38

Damith Benaragama

93 bronze badges

Collectives™ on Stack Overflow

Hibernate, Query SQL with params. Bad Performance

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related