The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

解決一個使用 OracleParameter 影響資料庫查詢效能的問題

我們最近有個專案遇到一個 Oracle 資料庫查詢效能的問題,由於問題有點複雜,我必須先將「前情提要」說明清楚。

運行環境

一些會用到的程式碼

以下是我使用 OracleCommand 與 OracleDataAdapter 的相關方法:

public static void FillDataTableBySQL(DataTable dt, string SQL, OracleParameter param1)
{
    OracleCommand cmd = new OracleCommand(SQL, GetConnection());
    cmd.Parameters.Add(param1);
    FillDataTableByCommand(dt, cmd);
}
public static void FillDataTableByCommand(DataTable dt, OracleCommand cmd)
{
    OracleDataAdapter da = new OracleDataAdapter(cmd);
    da.Fill(dt);
}

我用來進行資料查詢的程式碼如下:

string SQL = "SELECT * FROM mytable WHERE Field1 = :Field1";

OracleParameter param1 = new OracleParameter("Field1", OracleDbType.NVarchar2, 20);
param1.Value = "TEST";

Utils.FillDataTableBySQL(dt, SQL, param1);

各位看官目前看到這兩段程式會覺得有問題嗎?

我的 mytable 表格中有數十萬筆的資料,Field1 的索引原本沒有加上,所以這段 SQL 的查詢時間很久,大約每次查詢會花上 20 秒。

但是當我請 Oracle DBA 加上索引(INDEX)後,程式執行的查詢速度完全沒改變,每次查詢一樣會花上 20 秒,但我用 SQL Developer 工具用完全相同的 SQL 語法手動查詢資料時,每次查詢都只要 0.01 秒以內就可以查到,這真的是鬼打牆了!

我們接著開啟 ASP.NET 的 Trace 功能檢查程式中各個環節,看哪一段花費的時間最長,最後發現執行時間最長的地方在 da.Fill(dt); 這一段,但是這一段要怎麼調阿?所有的地方都執行很快,只有 da.Fill(dt); 這一段在慢,這只能讓我懷疑 SQL 語法是不是有問題,但這麼簡單的一句 SELECT 查詢有什麼好改的呢??

接著我們就測試不使用 OracleParameter 帶參數的方式寫看看,直接將 SQL 傳進 OracleCommand 進行查詢,用自行組合 SQL 字串的方式寫,結果速度奇快,跟使用 SQL Developer 工具的查詢速度如出一轍,但我再怎麼樣都不能接受用自行組合 SQL 字串的方式任何查詢,所以還是繼續研究問題發生的原因與解決辦法。

說實在的,我們幾乎將所有能想到的開發方式都用上了,花了好幾天的時間一直不斷的嘗試不同的寫法,也上網找到了跟我們遇到一樣問題的討論,可惜並沒有任何實用的解答。

直到最後 (五天後) 我們終於嘗試出可以擁有正常執行效率且繼續使用 OracleParameter 的方法了:

string SQL = "SELECT * FROM mytable WHERE Field1 = :Field1";

OracleParameter param1 = new OracleParameter("Field1", "TEST");

Utils.FillDataTableBySQL(dt, SQL, param1);

我不知道該說些什麼,因為怎麼想都覺得沒道理阿,這兩個寫法到底哪裡不一樣阿!?

  1. 一個是先 new 一個 OracleParameter 並指定欄位、型態、與大小,然後再設定其值。
  2. 一個是直接 new 一個 OracleParameter 並指定欄位與值。

為什麼用了第一種方法感覺上沒使用「索引」進行查詢,而用了第二種雷同的方法就會使用索引呢?

光這個問題搞了我們五天時間耶,這到底是 ODP.NET 的 Bug?還是 Oracle Database Server 的問題呢?有沒有人可以告訴我呢?

總之問題圓滿解決,也可以給客戶一個交代了,但心中依然留存一團迷霧(mist),留待日後再研究吧......