CODEWITHSUNDEEP
javahibernatelistjdbc
ares
ares

Reputation: 4413

java: ResultSet iteration vs List iteration : Is there a performance difference

I have to make a reporting utility which takes data from a very old and large table. My search criteria will take out say a million records at a time which later be used for some crap IO operation. I have the option of using JDBC which will give me a ResultSet or Hibernate which will give me List. I want to know will there be a performance difference between the two while iteration.

Upvotes: 1

Views: 1667

Answers (1)

gpeche
gpeche

Reputation: 22514

That is dependent on:

  • Hibernate version
  • DBMS and version
  • JDBC driver and version

How it often works is: if you create your Statements with:

PreparedStatement stmt = con.prepareStatement(sql,
                                              ResultSet.TYPE_FORWARD_ONLY,
                                              ResultSet.CONCUR_READ_ONLY);

then decent DBMS / drivers will stream big queries, and the memory impact will be small, at the cost of holding the Connection for longer. If you get the List for a big query with Hibernate, it will try to load the entire result set in memory at once, and if GC kicks in, the whole thing will crawl at best and will crash at worst. So for big result sets JDBC will be the better option.

Now, if you don't actually mind having a List with Hibernate, you can work with ScrollableResults. See this question: even if the approach did not work for that particular case, that technique will work right on every DBMS/driver combination that the JDBC approach would work right (after all it is just a thin Hibernate layer over the pure JDBC approach explained above). And you also get the ORM part of Hibernate.

Upvotes: 2

Related Questions