JDBC讀取數(shù)據(jù)優(yōu)化-fetch size

最近由于業(yè)務(wù)上的需求，一張舊表結(jié)構(gòu)中的數(shù)據(jù)，需要提取出來(lái)，根據(jù)規(guī)則，導(dǎo)入一張新表結(jié)構(gòu)中，開(kāi)發(fā)同學(xué)寫了一個(gè)工具，用于實(shí)現(xiàn)新舊結(jié)構(gòu)的transformation，

實(shí)現(xiàn)邏輯簡(jiǎn)單，就是使用jdbc從A表讀出數(shù)據(jù)，做了一些處理，再存入新表B中，發(fā)現(xiàn)讀取舊表的操作，非常緩慢，無(wú)法滿足要求。

讀取數(shù)據(jù)的示例代碼，

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語(yǔ)句讀取10000條記錄，其中，

Interval1=160ms
Interval2=29252ms

執(zhí)行executeQuery()這個(gè)SQL檢索的時(shí)間為160毫秒。

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

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

上面讀取10000條記錄，每一次rs.next時(shí)間只有3毫秒，但是由于需要10000次，所以才需要30秒，我們可以猜測(cè)，是否有可能每一次rs.next的執(zhí)行，均需要和數(shù)據(jù)庫(kù)交互，因?yàn)槿绻麅H是字符串操作，不應(yīng)該是這個(gè)數(shù)量級(jí)。

看一下官方文檔的描述，《Database JDBC Developer's Guide》有一節(jié)介紹了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默認(rèn)每執(zhí)行一次檢索，會(huì)從游標(biāo)中提取10行記錄，10就是默認(rèn)的row fetch size值，通過(guò)設(shè)置row fetch size，可以改變每次和數(shù)據(jù)庫(kù)交互，提取出來(lái)的記錄行總數(shù)。需要注意的是，需要在獲得檢索結(jié)果集之前，設(shè)置fetch size，否則就是無(wú)效。

可以使用如下方法設(shè)置，

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

簡(jiǎn)單來(lái)講，F(xiàn)etch相當(dāng)于讀緩存，默認(rèn)Fetch Size值是10，讀取10000條記錄，一次數(shù)據(jù)庫(kù)交互，即rs.next的操作，ResultSet會(huì)一次性從數(shù)據(jù)庫(kù)服務(wù)器，得到10條記錄，下次執(zhí)行rs.next，就直接使用內(nèi)存讀取，不用和數(shù)據(jù)庫(kù)交互了，但總計(jì)需要有1000次交互，如果使用setFetchSize設(shè)置Fetch Size為10000，則只需要一次數(shù)據(jù)庫(kù)交互，本地緩存10000條記錄，每次執(zhí)行rs.next，只是內(nèi)存操作，不會(huì)有數(shù)據(jù)庫(kù)網(wǎng)絡(luò)消耗，效率就會(huì)高些。但需要注意的是，F(xiàn)etch Size值越高則占用內(nèi)存越高，要避免出現(xiàn)OOM錯(cuò)誤。

方案1：

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

即在執(zhí)行ps.executeQuery()之后，對(duì)rs設(shè)置值10000，統(tǒng)計(jì)如下，

執(zhí)行executeQuery()這個(gè)SQL檢索的時(shí)間為174毫秒。

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

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

方案2：

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

即在執(zhí)行conn.prepareStatement(sql)之后，執(zhí)行ps.executeQuery()之前，對(duì)rs設(shè)置值為10000范圍，統(tǒng)計(jì)如下，

執(zhí)行executeQuery()這個(gè)SQL檢索的時(shí)間為267毫秒。

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

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

針對(duì)方案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.

執(zhí)行查詢之后，對(duì)結(jié)果集設(shè)置setFetchSize，會(huì)影響任何接下來(lái)的數(shù)據(jù)庫(kù)交互過(guò)程獲得更多的記錄行數(shù)，以及之后的fetch提取。

針對(duì)方案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.

執(zhí)行查詢之前，設(shè)置setFetchSize，表示每次和數(shù)據(jù)庫(kù)交互，得到記錄行數(shù)。

綜上所述，建議執(zhí)行SQL之前，設(shè)置此值，效率提升最高。

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

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()

總結(jié)：

1. Fetch相當(dāng)于讀緩存，如果使用setFetchSize設(shè)置Fetch Size為10000，本地緩存10000條記錄，每次執(zhí)行rs.next，只是內(nèi)存操作，不會(huì)有數(shù)據(jù)庫(kù)網(wǎng)絡(luò)消耗，效率就會(huì)高些。但需要注意的是，F(xiàn)etch Size值越高則占用內(nèi)存越高，要避免出現(xiàn)OOM錯(cuò)誤。

2. 建議執(zhí)行SQL語(yǔ)句之前設(shè)置，即ps.executeQuery();之前使用setFetchSize()函數(shù)設(shè)置。