CODEWITHSUNDEEP
performanceoracle-databasehibernatejpaglassfish
Javi Pedrera
Javi Pedrera

Reputation: 2095

Hibernate, Query SQL with params. Bad Performance

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

Response time:

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

Upvotes: 2

Views: 2822

Answers (2)

Damith Benaragama
Damith Benaragama

Reputation: 9

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

please refer to following for more details

Upvotes: 1

ben75
ben75

Reputation: 28706

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.

Similar question here

Upvotes: 2

Related Questions