I want to optimize an application which performs extensive fetching of a single rows inside a result list. I use Hibernate 3.2.5 with Derby 10.4.2.1.

I want to reduce the number of network operations for this query by using a named query:
@NamedQuery(
name="TraceValue_get",
query="SELECT v.traceValue FROM TraceValue v " +
"WHERE v.traceDescription = :traceDescription")

I then create a query once with:
Query q = em.createNamedQuery("TraceValue_get");
q.setMaxResults(1);

Then I fetch the values in a loop with:
q.setParameter("traceDescription", desc);
q.setFirstResult(offset);
Double result = (Double)valQuery.getSingleResult();

I checked the generated data packets and realized that 9 packets are created. (see log below). What surprises me is that the SQL statement seems to be transmitted and regenerated on each call.

I also tried a version with Extra-Lazy fetching, also there I need 7 packets and the SQL statement seems to be transmitted and compiled for each single value.

From my understanding, the optimum would be to perform this task with 3 packets. How to improve this?


---- Network log ----
14 25.772182 10.10.0.6 10.10.0.1 DRDA PRPSQLSTT | SQLATTR | SQLSTT | DSCSQLSTT
15 25.918529 10.10.0.1 10.10.0.6 DRDA SQLDARD | SQLDARD
16 25.919877 10.10.0.6 10.10.0.1 DRDA OPNQRY | SQLDTA
17 26.076517 10.10.0.1 10.10.0.6 DRDA OPNQRYRM | QRYDSC | QRYDTA
18 26.078035 10.10.0.6 10.10.0.1 DRDA CNTQRY
19 26.216524 10.10.0.1 10.10.0.6 DRDA QRYDTA
20 26.217104 10.10.0.6 10.10.0.1 DRDA CLSQRY | RDBCMM
21 26.346532 10.10.0.1 10.10.0.6 DRDA SQLCARD | ENDUOWRM | SQLCARD
22 26.538589 10.10.0.6 10.10.0.1 TCP 51958 > tlisrv [ACK] Seq=1601 Ack=8422 Win=4068 Len=0

Sorry, I had the wrong log. Executing the named query generates just 7 packets. But the statement still get's recompiled.

---- Network log ----
16 41.095248 10.10.0.6 10.10.0.1 DRDA PRPSQLSTT | SQLATTR | SQLSTT | DSCSQLSTT
17 41.251245 10.10.0.1 10.10.0.6 DRDA SQLDARD | SQLDARD
18 41.253025 10.10.0.6 10.10.0.1 DRDA OPNQRY | SQLDTA
19 41.391265 10.10.0.1 10.10.0.6 DRDA OPNQRYRM | QRYDSC | QRYDTA
20 41.391826 10.10.0.6 10.10.0.1 DRDA RDBCMM
21 41.511263 10.10.0.1 10.10.0.6 DRDA ENDUOWRM | SQLCARD
22 41.710313 10.10.0.6 10.10.0.1 TCP 52169 > tlisrv [ACK] Seq=1251 Ack=1161 Win=22163 Len=0