Having strange performance issue using Hibernate 3.3.2GA behind JPA (and the rest of the Hibernate packages included in JBoss 5.)
I'm using Native Query, and assembling SQL into a prepared statement.
EntityManager em = getEntityManager(MY_DS);
final Query query = em.createNativeQuery(fullSql, entity.getClass());
The SQL has a lot of joins, but is actually very basic, with a single parameter. Like:
SELECT field1, field2, field3 FROM entity left join entity2 on... left join entity3 on
WHERE stringId like ?
and the query runs in under a second on MSSQL Studio.
If I add
query.setParameter(0, "ABC123%");
The query will pause 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 replace the "?" with the value (making it not a Prepared Statement, but just a straight SQL query.
fullSql = fullSql.replace("?", "'ABC123%'");
the query will complete in less that a second.
I would really prefer to us a Prepared Statement (the input for the parameters is being extracted from user data) to prevent injection attacks.
Tracing down the slow point in the code, I arrived deep within the jtds-1.2.2 package. The offending line seems to be SharedSocket line 841 "getIn().readFully(hdrBuf);" Nothing really obvious there though...
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.");
}