JDBC resultset setfetchsize

 

Connection conn = NULL; <br/> resultset rs = NULL; <br/> preparedstatement stmt = NULL; <br/> try <br/> {<br/> conn = dbmgr. getconnection (); <br/> stmt = Conn. preparestatement (track_ SQL); <br/> rs = dbmgr.exe cutequery (Conn, stmt); </P> <p> while (RS. next () <br/>{< br/> ret. add (RS. getlong (1); <br/>}< br/>}finally <br/>{< br/> If (stmt! = NULL) <br/> stmt. Close (); <br/> If (RS! = NULL) <br/> Rs. Close (); <br/> If (Conn! = NULL) <br/> dbmgr. releaseconnection (conn); <br/>} 

When testing in Oracle, above loop is very slow. If add setfetchsize before next, the performance improve significantly

 

Rs. setfetchsize (dbmgr. fetch_size_large_row ); 

 

Resultset interface also provides batch retrieval facility like statement as mentioned above. It overrides the statement behaviour.

Initially find the default size by using

Resultset. getfetchsize (); and then set the size as per requirement

Resultset. setfetchsize (50 );

This feature significantly improves performance when you are dealing with retrieval of large number of rows like search functionality.

 

Todo: Reading full tips

Http://www.precisejava.com/javaperf/j2ee/JDBC.htm#JDBC114