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

真的有感!!

Post a comment