JDBC ResultSet setFetchSize

提升Oracle查询性能

最新推荐文章于 2024-01-17 23:02:29 发布

原创最新推荐文章于 2024-01-17 23:02:29 发布 · 4.7k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#jdbc #performance #null #interface #testing #search

java 专栏收录该内容

62 篇文章

订阅专栏

本文介绍了一种通过调整ResultSet的fetch size来显著提高Oracle数据库查询性能的方法，并解释了如何利用批处理检索设施进一步优化大量数据的检索过程。

Connection conn = null; ResultSet rs = null; PreparedStatement stmt = null; try { conn = dbMgr.getConnection(); stmt = conn.prepareStatement(track_sql); rs = dbMgr.executeQuery(conn, stmt); while (rs.next()) { ret.add(rs.getLong(1)); } } finally { if (stmt != null) stmt.close(); if (rs != null) rs.close(); if (conn != null) dbMgr.releaseConnection(conn); }

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