Organizational Research By

Surprising Reserch Topic

slow performance on hibernate java but fast when i use toad with the same nati


slow performance on hibernate java but fast when i use toad with the same nati  using -'java,oracle,hibernate'

I've detected a performance problem with hibernate and native queries on Oracle. When I execute a complex SQL query with several parameters on TOAD I get the result in miliseconds. However, when I execute the same query using Hibernate this time is incremented hugely (up to four seconds or even more).

My SQL query is rather complex, return an unique value (so, the problem is not related with the time necessary to instation classes) and it contains several parameters with the the format ':nameParameter'. This query is stored in a String. For example,

String myNamedNativeQuery = "select count(*) from tables "+
                            "where column1 = :nameParameter1 "+
                            "and column2 = :nameParameter2";  
                            //actually my sentence is much more complex!!


When I execute the sentence on TOAD it is resolved in few miliseconds. But using this sentence with Hibernate

SQLQuery query = session.createSQLQuery("myNamedNativeQuery");

query.setParameter(nameParameter1, value1);
query.setParameter(nameParameter2, value2);

query.uniqueResult();


are necessary several seconds to get the same result.

I realized if I replaced the parameters directly on the native query and then I execute the sentence using Hibernate the time decreases drastically. It would be something like that:

String strQuery = session.getNamedQuery("myNamedNativeQuery").getQueryString();

myNamedNativeQuery = myNamedNativeQuery.replace("nameParameter1", value1);
myNamedNativeQuery = myNamedNativeQuery.replace("nameParameter2", value2);

SQLQuery query = session.createSQLQuery("myNamedNativeQuery");
query.uniqueResult();


Anybody knows what's happening??

Thanks in advance.

PS: The Oracle version is 9i and Hibernate 3.2
    

asked Oct 13, 2015 by mcasudhir
0 votes
1 view



Related Hot Questions

2 Answers

0 votes

I think what's happening with this code :

SQLQuery query = session.createSQLQuery("myNamedNativeQuery");
query.setParameter(nameParameter1, value1);
query.setParameter(nameParameter2, value2);
query.uniqueResult(); 

is this:

at line 1 : a query plan is created based on some expected values for your named parameters.

at line 4 : the query is executed with value1 and value2, but those values are not "good values" for the query plan that was elaborate at line 1 and so, the database is executing a very inappropriate plan for the actual values and it takes a lot of time.

Why ?

Looking at the source code of HibernateSessionImpl.createSQLQuery(...) I found this line of code:

SQLQueryImpl query = new SQLQueryImpl(
                sql,
                        this,
                        factory.getQueryPlanCache().getSQLParameterMetadata( sql )
        );

which is calling getQueryPlanCache() with some parameterMetaData. I assume that this metadata is not good enough.

answered Oct 13, 2015 by sandeep bhadauria
0 votes

My answer to you is:

Remove all bind parameters and use StatelessSession instead of Session

Use SQLQuery instead of query with full SQL including parameter values

StatelessSession session = sessionFactory.openStatelessSession();

I had similar problem and till I get better solution,this is what I managed to make it work. See Hibernate parameterized sql query slow and active oracle sessions

answered Oct 13, 2015 by android_master

...