Hi Lukas: Memory is not a big constraints for us as RAM is cheaper. I have done various load testing with different pre-fetch size. In oracle loading 10,000 rows with fetch size as "10" is 10 times slower than fetch size with "100". I could setup defaultRowPrefetch while making connection but in some cases we configure WebSphere connection pool where passing defaultRowPrefetch property is not easy hence I am looking for jOOQ API level control to set fetch size.
Regards, Venkat On Monday, 28 October 2013 09:10:00 UTC-4, Lukas Eder wrote: > > Hi Venkat, > > In our E-Banking application (where I've previously worked), we've never > experienced any issue with ojdbc, even for large-ish ResultSets of 400k > records in batch jobs. I wasn't working on that code, so it might as well > be that some sort of tuning was actually made, that I'm not aware of. Just > to understand the use-case, what kind of ResultSet size are you expecting > to generate? And what are your JVM memory limitations (overall / per > session)? > > Cheers > Lukas > > 2013/10/28 Venkat Sadasivam <[email protected] <javascript:>> > >> Hi Lukas: >> >> Yes, it is a major factor in Oracle's JDBC driver performance. >> >> http://webmoli.com/2009/02/01/jdbc-performance-tuning-with-optimal-fetch-size/ >> >> Thanks for adding into 3.3 release. >> Venkat >> >> On Monday, 28 October 2013 03:18:05 UTC-4, Lukas Eder wrote: >> >>> Hi Venkat, >>> >>> Yes you can. This is not documented prominently, but it is mentioned >>> here: >>> - >>> http://www.jooq.org/doc/3.2/**manual/sql-execution/fetching<http://www.jooq.org/doc/3.2/manual/sql-execution/fetching/> >>> - http://www.jooq.org/javadoc/**latest/org/jooq/ResultQuery.** >>> html#fetchLazy(int)<http://www.jooq.org/javadoc/latest/org/jooq/ResultQuery.html#fetchLazy(int)> >>> >>> // Create a "lazy" Cursor, that keeps an open underlying JDBC >>> ResultSetCursor<R> fetchLazy();Cursor<R> fetchLazy(int fetchSize); >>> >>> >>> Interestingly, though, this had been implemented only for lazy fetching, >>> at the time. I think it should be supported for all sorts of ResultQuery. I >>> will fix this in jOOQ 3.3: >>> https://github.com/jOOQ/jOOQ/**issues/2806<https://github.com/jOOQ/jOOQ/issues/2806> >>> >>> If you need more control over the JDBC fetch size flag, you can >>> implement an ExecuteListener: >>> >>> public class FetchSizeListener extends DefaultExecuteListener { >>> >>> @Override >>> public void prepareEnd(ExecuteContext ctx) { >>> try { >>> ctx.statement().setFetchSize(**myFetchSize); >>> } >>> catch (SQLException e) { >>> throw new DataAccessException("Exception while setting fetch >>> size", e); >>> } >>> } >>> } >>> >>> >>> Hope this helps >>> Lukas >>> >>> >>> >>> 2013/10/28 Venkat Sadasivam <[email protected]> >>> >>>> Lukas: >>>> >>>> Are there any way to set fetch size in jOOQ API? >>>> http://docs.oracle.com/javase/**7/docs/api/java/sql/Statement.** >>>> html#setFetchSize(int)<http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setFetchSize(int)> >>>> >>>> Thanks, >>>> Venkat >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "jOOQ User Group" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to jooq-user+...@**googlegroups.com. >>>> >>>> For more options, visit >>>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> >>>> . >>>> >>> >>> -- >> You received this message because you are subscribed to the Google Groups >> "jOOQ User Group" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> For more options, visit https://groups.google.com/groups/opt_out. >> > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
