0

I have a Query in hibernate which I believe is because of the VARCHAR and NVARCHAR discrepancies in the driver and MsSql discussed here Getting Hibernate and SQL Server to play nice with VARCHAR and NVARCHAR. I understand that this issue is not with Hibernate but am confused by the way hibernate deals with the code below .

When we ran the Queries below (using set parameter and without it), on a DB with VARCHAR column type for the column 'code' , we found that appending the string in HQL works lot faster that setParameter(or Criteria Query which should have been faster than HQL) .

Query createQuery = session.createQuery("from abc where code='"+substring+"'");
/*Query createQuery = session.createQuery("from abc where   code=:substring");
                createQuery.setParameter("substring","Test");*/
                return createQuery.list();

Please let me know how this would make a difference.

Community
  • 1
  • 1
Aravind A
  • 9,507
  • 4
  • 36
  • 45

1 Answers1

0

The execution plan computed by the database is probably different between the two queries. A similar problem is described in this question, and my DBA already told me that the same problem can appear with Oracle as well.

Community
  • 1
  • 1
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • But why is my Criteria Query slower ? . I am confused because setting the sendStringParametersAsUnicode=false gives me similar performance as the query in which I append parameters . setParameter in HQL seems to give the same performance as Criteria . So, is appending parameters directly to the Query doing something to help overcome the VARCHAR in DB issue ? – Aravind A Jul 01 '11 at 09:09
  • When you don't have any parameter in a HQL query (i.e., when you use string concatenation and don't have to call setParameter on the query), the JDBC driver doesn't have any parameter to send. In this case, sendStringParametersAsUnicode is irrelevant. When you have a parameterized HQL query, or when you use the Criteria API, Hibernate generates prepared statements and the JDBC driver sends parameters. In this case, sendStringParametersAsUnicode is relevant. – JB Nizet Jul 01 '11 at 12:21
  • @Nizet: Now I get it.Thanks a lot . – Aravind A Jul 04 '11 at 06:23