Native SQL witth bind parameters much slower than HQL

cpommier · **Joined:** Tue Jul 11, 2006 7:41 am **Posts:** 2

Hibernate version:
3.1
Name and version of the database you are using:
Postgresql 8.1.3

Hello,
I have a big query that I must implement as native SQL (it uses UNION).
If I implement this query cleanly, ie with binded parameters, it is awfully slow (> 15 seconds).
The query outputed with show_sql is fast (<1 second).

I can get the native sql query to be as fast as a direct SQL query (through psql) if I directly put the value of the parameters inside the sql String instead of using binded parameters.
For instance :

Code:

getSession().createSQLQuery("select {feat.* } \n" + 
        "from FEATURE feat  where feat.uniquename like \'B0510_contig_1887_fgenesh_Magnaporthe_transcript%\'   \n" + 
        "UNION\n" + 
        " select {feat.* } \n" + 
        "from FEATURE feat join FEATURE_SYNONYM fs using (feature_id) join SYNONYM s using (synonym_id) where s.name like \'B0510_contig_1887_fgenesh_Magnaporthe_transcript%\'  \n").addEntity("feat",FeatureImpl.class).list();

executes in less than a second, while :

Code:

getSession().createSQLQuery("select {feat.* } \n" + 
        "from FEATURE feat  where feat.uniquename like ?  \n" + 
        "UNION\n" + 
        " select {feat.* } \n" + 
        "from FEATURE feat join FEATURE_SYNONYM fs using (feature_id) join SYNONYM s using (synonym_id) where s.name like ? \n").addEntity("feat",FeatureImpl.class)
.setString(0,"B0510_contig_1887_fgenesh_Magnaporthe_transcript%")
.setString(1,"B0510_contig_1887_fgenesh_Magnaporthe_transcript%")
.list();

executes in 15 seconds

Am I missing something here?
Is there a way to get better performances with native SQL and binded parameters?

Thanks in advance for any help.

batmat · **Posted:** Tue Jul 11, 2006 1:56 pm

Well, I think you'll need someone with a DBA background to help, which I'm not. Maybe it's because parametrized queries are not managed samely by the db...

I don't believe it will change anything, but anyway, did you try using named parameters instead of numerically binded ones ? In fact, if the goal is to clarify your code, I think named params is even better : http://www.hibernate.org/hib_docs/v3/re ... parameters

max · **Posted:** Tue Jul 11, 2006 3:40 pm

its the db or the db driver that is to "blame" here.

cpommier · **Joined:** Tue Jul 11, 2006 7:41 am **Posts:** 2

Hello,
thanks for your suggestions. I have tried all three jdbc drivers (jdbc2 jdbc2EE and jdbc3), but the problem remains the same. I have also tried positionnal and nammed paramters.

I don't think that this problem is driver or db related.

If i do a simple HQL query with binded parameters it works like a charm, but the same query as native SQL is way too slow. So it is not a query problem, nor a parameter binding (at the driver level) problem.

The binding process is quick, it's the execution of a query which has received a parameter which is slow.

max · **Posted:** Mon Jul 17, 2006 5:52 am

we execute the query in the same manner....so don't know why the db takes a longer time to execute it.