Using Spring 3 to generate sql that uses cursors in Postgres

Question

I have java webapp that I've upgraded from spring 2.5.x to spring 3.x.

Everything post-upgrade works except that database load has gone up and queries are taking longer. The stack is Hibernate 3.6.10, Postgres 9.1, and Spring 2.5.4 / 3.0. The only thing that differs between pre / post upgrade is the spring jars and deps.

I've found that the queries generated under the older (faster) spring 2 setup are using cursors to return the results, while the newer (slower) spring 3 setup generates queries that don't.

For example:

Fast

LOG:  statement: DECLARE JDBC_CURS_660 CURSOR FOR select distinct virtualfle0_.VIRTUAL_FLEET_ID as VIRTUAL1_58_, virtualfle0_.CSO_ID as CSO2_58_, virtualfle0_.VIRTUAL_FLEET_NAME as VIRTUAL3_58_, virtualfle0_.SCHEDULE_ID as SCHEDULE4_58_ from EG_VIRTUAL_FLEET virtualfle0_ where (virtualfle0_.CSO_ID=2 ) limit 40; FETCH FORWARD 100 FROM JDBC_CURS_660

Slow

LOG:  execute S_158/C_159: select distinct virtualfle0_.VIRTUAL_FLEET_ID as VIRTUAL1_58_, virtualfle0_.CSO_ID as CSO2_58_, virtualfle0_.VIRTUAL_FLEET_NAME as VIRTUAL3_58_, virtualfle0_.SCHEDULE_ID as SCHEDULE4_58_ from EG_VIRTUAL_FLEET virtualfle0_ where (virtualfle0_.CSO_ID=$1 ) limit $2
DETAIL:  parameters: $1 = '2', $2 = '40'

The code that generates these queries comes from a hibernate criteria, and that code hasn't changed as part of the Spring upgrade.

Among my hibernate configs is this:

<property name="hibernate.jdbc.fetch_size">100</property>
<property name="hibernate.jdbc.batch_size">100</property>

So my question is, how does one get spring 3 to generate the correct sql that uses cursors?

depsypher · Accepted Answer · 2013-01-15 23:43:06Z

1

Ugh, ok. It turns out there was a jar I removed as part of the upgrade that had some postgres driver stuff in it (among other project-specific junk).

It probably contains a custom extension to the standard driver for wrapping each of these selects in a cursor, though it's hard to say without having the source :( In any case, putting that jar back on the classpath returns it to the needed behavior.

answered Jan 15, 2013 at 23:43

depsypher

1,25915 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Spring 3 to generate sql that uses cursors in Postgres

Fast

Slow

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Fast

Slow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related