JDBC讀取數據優化-fetch size

最近由于業務上的需求，一張舊表結構中的數據，需要提取出來，根據規則，導入一張新表結構中，開發同學寫了一個工具，用于實現新舊結構的transformation，

實現邏輯簡單，就是使用jdbc從A表讀出數據，做了一些處理，再存入新表B中，發現讀取舊表的操作，非常緩慢，無法滿足要求。

讀取數據的示例代碼，

conn = getConnection();
long start = System.currentTimeMillis();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
long mid_end = System.currentTimeMillis();
while (rs.next()) {
    list.add(rs.getString(1));
}
long end = System.currentTimeMillis();
rs.close();
System.out.println("Interval1=" + (mid_end - start));
System.out.println("Interval2=" + (end - mid_end));

SQL語句讀取10000條記錄，其中，

Interval1=160ms
Interval2=29252ms

執行executeQuery()這個SQL檢索的時間為160毫秒。

執行10000次rs.next以及rs.getString(1)的用時約為30秒，平均1條記錄3毫秒。

如何才能提高讀取的效率？

上面讀取10000條記錄，每一次rs.next時間只有3毫秒，但是由于需要10000次，所以才需要30秒，我們可以猜測，是否有可能每一次rs.next的執行，均需要和數據庫交互，因為如果僅是字符串操作，不應該是這個數量級。

看一下官方文檔的描述，《Database JDBC Developer's Guide》有一節介紹了Fetch Size，

By default, when Oracle JDBC runs a query, it retrieves a result set of 10 rows at a time from the database cursor. This is the default Oracle row fetch size value. You can change the number of rows retrieved with each trip to the database cursor by changing the row fetch size value.

Standard JDBC also enables you to specify the number of rows fetched with each database round-trip for a query, and this number is referred to as the fetch size. In Oracle JDBC, the row-prefetch value is used as the default fetch size in a statement object. Setting the fetch size overrides the row-prefetch setting and affects subsequent queries run through that statement object.

Fetch size is also used in a result set. When the statement object run a query, the fetch size of the statement object is passed to the result set object produced by the query. However, you can also set the fetch size in the result set object to override the statement fetch size that was passed to it.

Changes made to the fetch size of a statement object after a result set is produced will have no affect on that result set.

JDBC默認每執行一次檢索，會從游標中提取10行記錄，10就是默認的row fetch size值，通過設置row fetch size，可以改變每次和數據庫交互，提取出來的記錄行總數。需要注意的是，需要在獲得檢索結果集之前，設置fetch size，否則就是無效。

可以使用如下方法設置，

Setting the Fetch Size

The following methods are available in all Statement, PreparedStatement, CallableStatement, and ResultSet objects for setting and getting the fetch size:

• void setFetchSize(int rows) throws SQLException

• int getFetchSize() throws SQLException

簡單來講，Fetch相當于讀緩存，默認Fetch Size值是10，讀取10000條記錄，一次數據庫交互，即rs.next的操作，ResultSet會一次性從數據庫服務器，得到10條記錄，下次執行rs.next，就直接使用內存讀取，不用和數據庫交互了，但總計需要有1000次交互，如果使用setFetchSize設置Fetch Size為10000，則只需要一次數據庫交互，本地緩存10000條記錄，每次執行rs.next，只是內存操作，不會有數據庫網絡消耗，效率就會高些。但需要注意的是，Fetch Size值越高則占用內存越高，要避免出現OOM錯誤。

方案1：

rs = ps.executeQuery();
rs.setFetchSize(10000);

即在執行ps.executeQuery()之后，對rs設置值10000，統計如下，

執行executeQuery()這個SQL檢索的時間為174毫秒。

執行10000次rs.next以及rs.getString(1)的用時約為190毫秒。

相比之前執行10000次rs.next，用了30秒，提高了將近150倍。

方案2：

ps = conn.prepareStatement(sql);
ps.setFetchSize(10000);

即在執行conn.prepareStatement(sql)之后，執行ps.executeQuery()之前，對rs設置值為10000范圍，統計如下，

執行executeQuery()這個SQL檢索的時間為267毫秒。

執行10000次rs.next以及rs.getString(1)的用時約為87毫秒。

相比方案2，總用時幾乎一致，但SQL執行和rs.next遍歷的用時，有些區別。

針對方案1，

After you have run the query, you can call  setFetchSize  on the result set object to override the statement object fetch size that was passed to it. This will affect any subsequent trips to the database to get more rows for the original query, as well as affecting any later refetching of rows.

執行查詢之后，對結果集設置setFetchSize，會影響任何接下來的數據庫交互過程獲得更多的記錄行數，以及之后的fetch提取。

針對方案2，

To set the fetch size for a query, call  setFetchSize  on the statement object prior to running the query. If you set the fetch size to N, then N rows are fetched with each trip to the database.

執行查詢之前，設置setFetchSize，表示每次和數據庫交互，得到記錄行數。

綜上所述，建議執行SQL之前，設置此值，效率提升最高。

對于PrepareStatement、ResultSet和Statement，均有這一個方法，有一點出入的，就是默認值設置(0)，從代碼中使用getFetchSize()，得到的值均為10，不知道是我理解錯了，還是有其他含義？歡迎各位指教。

PrepareStatement

• setFetchSize

  
  void setFetchSize(int rows)
  
  
             throws SQLException
  
  

  Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for  ResultSet objects generated by this  Statement . If the value specified is zero, then the hint is ignored. The default value is zero.

• Parameters:

• rows - the number of rows to fetch

• Throws:

• SQLException - if a database access error occurs, this method is called on a closed Statement or the condition rows >= 0 is not satisfied.

• Since:

• 1.2

• See Also:

• getFetchSize()

ResultSet

• setFetchSize

  
  void setFetchSize(int rows)
  
  
             throws SQLException
  
  

  Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this  ResultSet  object. If the fetch size specified is zero, the JDBC driver ignores the value and is free to make its own best guess as to what the fetch size should be.  The default value is set by the  Statement  object that created the result set.  The fetch size may be changed at any time.

• Parameters:

• rows - the number of rows to fetch

• Throws:

• SQLException - if a database access error occurs; this method is called on a closed result set or the condition rows >= 0 is not satisfied

• Since:

• 1.2

• See Also:

• getFetchSize()

Statement

• setFetchSize

  
  void setFetchSize(int rows)
  
  
             throws SQLException
  
  

  Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for  ResultSet objects generated by this  Statement . If the value specified is zero, then the hint is ignored. The default value is zero.

• Parameters:

• rows - the number of rows to fetch

• Throws:

• SQLException - if a database access error occurs, this method is called on a closed Statement or the condition rows >= 0 is not satisfied.

• Since:

• 1.2

• See Also:

• getFetchSize()

總結：

1. Fetch相當于讀緩存，如果使用setFetchSize設置Fetch Size為10000，本地緩存10000條記錄，每次執行rs.next，只是內存操作，不會有數據庫網絡消耗，效率就會高些。但需要注意的是，Fetch Size值越高則占用內存越高，要避免出現OOM錯誤。

2. 建議執行SQL語句之前設置，即ps.executeQuery();之前使用setFetchSize()函數設置。