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


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");

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
answered Oct 13, 2015 by sandeep bhadauria
0 votes
answered Oct 13, 2015 by android_master