讓ODP.NET查詢快10倍的小密技-FetchSize
3 |
同事報案,某專案使用ODP.NET+Dapper查詢一萬筆資料要耗時三分鐘,而同樣查詢丟到PL/SQL Developer跑只要15秒。為了洗刷.NET效能不佳的罪名,立刻出發調查。
我實做一個簡單測試重現問題,在我的i7機器執行,查詢取回10,691筆耗時34.794秒;用PL/SQL Developer查詢測得11.453秒,足足慢了3倍。
using Oracle.DataAccess.Client;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Test
{
class Program
{
static void Main(string[] args)
{
var cs = "data source=blah;user id=foo;password=bar";
using (var cn = new OracleConnection(cs))
{
var sw = new Stopwatch();
sw.Start();
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = @"
SELECT A_Lot_Of_Columns
FROM Some_Table
WHERE Some_Column = 'A'
AND Some_Flag <> 'F'
ORDER BY Another_Column";
var dr = cmd.ExecuteReader();
var count = 0;
while (dr.Read())
count++;
sw.Stop();
Console.WriteLine("{1:n0} rows in {0:n0} ms",
sw.ElapsedMilliseconds, count);
}
Console.ReadLine();
}
}
}
用ODP.NET+Slow關鍵字爬文,發現一椿天大的秘密。使用ODP.NET這麼多年,我竟不知有個參數-FetchSize會戲劇化地影響ODP.NET查詢效能。
Oracle雜誌有篇文章ODP.NET: Improve ODP.NET Performance提到加速ODP.NET的三樣法寶:Connection Pooling、FetchSize以及Statement Cache。其中FetchSize指的是每次DataReader向DB讀取資料一次取回的資料量,預設FetchSize為131072(128KB)。換句話說,若查詢結果有128MB,則Reader要從DB讀取1024次才能把資料讀完。每次發動讀取都有收發封包及處理的額外成本(Overhead),增加每次讀取資料量可以降低讀取次數,即可提升效能(跟JavaScript、CSS打包、圖檔Sprite相同道理)。然而,增加每次讀取資料量將耗用較多記憶體(例如:只有1KB資料,ODP.NET也得向系統要求1MB記憶體,雖然用完可回收,但要求及回收大量記憶體會消耗CPU/IO資源),故開發人員要在效能與記憶體使用間拿捏。依我個人看法,在這記憶體成本日益下降的時代,處理速度與使用者滿意度、老闆的笑容與自己的荷包息息相關,拿記憶體換效能絕對划算。
以上述程式為例,我們可修改程式,在dr.Read()之前調整FetchSize,讓資料區大小足夠每次讀取入2,000筆:
var dr = cmd.ExecuteReader();
//指定FetchSize,每次讀取2000筆
dr.FetchSize = dr.RowSize * 2000;
var count = 0;
while (dr.Read())
count++;
原本讀完10,691筆要34秒,猜看看變幾秒?
3.387秒!
我驚呆了!快了10倍,這麼多年來,居然不知道有這招?而FetchSize在單筆資料量大、筆數龐大的場合,最能展現威力。在本例中,RowSize約1K,1萬筆資料量約10MB,原本128KB要讀78次,修改FetchSize後只需讀6次,產生10倍的速度差異。
未來大家使用ODP.NET讀取大量資料如遇效能不佳,加入一行FetchSize設定就可能脫胎換骨,可多加利用。
Comments
# by Zakk
雖然已經很久沒有當Oracle的Client了 但是看到這篇文章還是很興奮
# by 阿翰
在這記憶體成本日益下降的時代... 現在看還唏噓,記憶體比CPU貴的時代 (FXXX!
# by EricWang
真的有感!!