JPA (Hibernate) Native query for prepared SLOW statement

Question

There is a strange performance problem using Hibernate 3.3.2GA per JPA (and the rest of the Hibernate packages included in JBoss 5.)

I use Native Query and compile SQL into a prepared statement.

EntityManager em = getEntityManager(MY_DS); final Query query = em.createNativeQuery(fullSql, entity.getClass()); 

SQL has many connections, but is actually very simple, with one parameter. How:

 SELECT field1, field2, field3 FROM entity left join entity2 on... left join entity3 on WHERE stringId like ? 

and the query is executed under the second one in MSSQL Studio.

If I add

 query.setParameter(0, "ABC123%"); 

Request will be suspended for 9 seconds.

 2012-01-20 14:36:21 - TRACE: - AbstractBatcher.getPreparedStatement:(484) | preparing statement 2012-01-20 14:36:21 - TRACE: - StringType.nullSafeSet:(133) | binding 'ABC123%' to parameter: 1 2012-01-20 14:36:30 - DEBUG: - AbstractBatcher.logOpenResults:(382) | about to open ResultSet (open ResultSets: 0, globally: 0) 

However, if I just replaced the "?" with a value (which makes it not a prepared statement, but just a direct SQL query.

 fullSql = fullSql.replace("?", "'ABC123%'"); 

the request will be completed in less than a second.

I would prefer a prepared expression (input for parameters is extracted from user data) to prevent injection attacks.

Tracking the slow point in the code, I arrived deep in the jtds-1.2.2 package. The line of violation is the SharedSocket 841 line "getIn (). ReadFully (hdrBuf);" There is nothing really obvious ...

 private byte[] readPacket(byte buffer[]) throws IOException { // // Read rest of header try { getIn().readFully(hdrBuf); } catch (EOFException e) { throw new IOException("DB server closed connection."); } 

Arrived on this stack ...

  at net.sourceforge.jtds.jdbc.SharedSocket.readPacket(SharedSocket.java:841) at net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket(SharedSocket.java:722) at net.sourceforge.jtds.jdbc.ResponseStream.getPacket(ResponseStream.java:466) at net.sourceforge.jtds.jdbc.ResponseStream.read(ResponseStream.java:103) at net.sourceforge.jtds.jdbc.ResponseStream.peek(ResponseStream.java:88) at net.sourceforge.jtds.jdbc.TdsCore.wait(TdsCore.java:3928) at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1045) at net.sourceforge.jtds.jdbc.TdsCore.microsoftPrepare(TdsCore.java:1178) at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareSQL(ConnectionJDBC2.java:657) at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:776) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1808) at org.hibernate.loader.Loader.doQuery(Loader.java:697) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259) at org.hibernate.loader.Loader.doList(Loader.java:2228) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125) at org.hibernate.loader.Loader.list(Loader.java:2120) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312) at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722) at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165) at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:67) 

Answer 1

I will leave this question and answer here if someone has the same problem in the future.

The problem is how JTDS drivers send parameter strings to MSSQL. Apparently, Java will try to send Unicode parameters by default, and MSSQL will translate it into Ascii. Why it takes 9 seconds, I don’t know.

There are many references to this there, but nothing helped until I was able to isolate that this is a problem with the driver for connecting MSSQL.

This link was useful:

[http://server.pramati.com/blog/2010/06/02/perfissues-jdbcdrivers-mssqlserver/]

This is a string using the Microsoft driver.

 jdbc:sqlserver://localhost\SQLEXPRESS; DatabaseName=TESTDB; sendStringParametersAsUnicode=false 

You just need to pass sendStringParametersAsUnicode = false passed to your driver setup, and you're fine.

Answer 2

Check the query plans that the SQL server produces. Prepared statements can be especially problematic.

Let me explain ...

If you do this:

 SELECT field1, field2, field3 FROM entity left join entity2 on... left join entity3 on WHERE stringId like 'ABC123%'; 

and you have an index on "stringId" The SQL server knows that it can use it.

However, if you do this:

 SELECT field1, field2, field3 FROM entity left join entity2 on... left join entity3 on WHERE stringId like ?; 

The SQL server does not know that it can use the index when it creates a prepared statement (since you can fill in the parameter "% ABC123" instead of "ABC123%") and thus you can choose a completely different query plan.

Answer 3

And another answer for people potentially using Oracle with a similar Unicode problem ...

Make sure someone has not set the oracle.jdbc.defaultNChar = true property

This is sometimes done to solve unicode problems, but this means that all columns are treated as nvarchars. If you have an index in a varchar column, it will not be used because oracle must use a function to convert character encoding.