同事報案,某專案使用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

真的有感!!

Post a comment