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

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價值的長期合作伙伴，公司提供的服務(wù)項目有：申請域名、網(wǎng)絡(luò)空間、營銷軟件、網(wǎng)站建設(shè)、山陽網(wǎng)站維護(hù)、網(wǎng)站推廣。

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

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

Interval1=160ms
Interval2=29252ms

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

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

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

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

看一下官方文檔的描述，《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í)行一次檢索，會從游標(biāo)中提取10行記錄，10就是默認(rèn)的row fetch size值，通過設(shè)置row fetch size，可以改變每次和數(shù)據(jù)庫交互，提取出來的記錄行總數(shù)。需要注意的是，需要在獲得檢索結(jié)果集之前，設(shè)置fetch size，否則就是無效。

可以使用如下方法設(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

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

方案1：

---

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

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

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

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

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

---

方案2：

---

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

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

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

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

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

---

針對方案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í)行查詢之后，對結(jié)果集設(shè)置setFetchSize，會影響任何接下來的數(shù)據(jù)庫交互過程獲得更多的記錄行數(shù)，以及之后的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.

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

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

對于PrepareStatement、ResultSet和Statement，均有這一個方法，有一點(diǎn)出入的，就是默認(rèn)值設(shè)置(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()

總結(jié)：

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

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

網(wǎng)站標(biāo)題：JDBC讀取數(shù)據(jù)優(yōu)化-fetchsize-創(chuàng)新互聯(lián)
文章起源：http://weahome.cn/article/csehci.html