讓ODP.NET查詢快10倍的小密技－FetchSize
source link: https://blog.darkthread.net/blog/odp-net-fetchsize/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
讓ODP.NET查詢快10倍的小密技－FetchSize-黑暗執行緒
同事報案，某專案使用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設定就可能脫胎換骨，可多加利用。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK