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

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

**運行環境**

-   資料庫版本：Oracle 11g
-   用戶端版本：[Oracle Database 10g Release 2 (10.2.0.1.0)](https://www.oracle.com/database/technologies/oracle-database-software-downloads.html)
-   Oracle.DataAccess.dll 組件版本：10.2.0.100  ( [Oracle Data Provider for .NET](http://www.oracle.com/technology/tech/windows/odpnet/index.html) )

**一些會用到的程式碼**

以下是我使用 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](http://www.oracle.com/technology/products/database/sql_developer/index.html) 工具用完全相同的 SQL 語法手動查詢資料時，每次查詢都只要 0.01 秒以內就可以查到，這真的是鬼打牆了！

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

接著我們就**測試**不使用 OracleParameter 帶參數的方式寫看看，直接將 SQL 傳進 OracleCommand 進行查詢，用自行組合 SQL 字串的方式寫，結果速度奇快，跟使用 [SQL Developer](http://www.oracle.com/technology/products/database/sql_developer/index.html) 工具的查詢速度[如出一轍](https://dict.idioms.moe.edu.tw/idiomView.jsp?ID=1162)，但我再怎麼樣都不能接受用自行組合 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](http://www.oracle.com/technology/tech/windows/odpnet/index.html) 的 Bug？還是 Oracle Database Server 的問題呢？有沒有人可以告訴我呢？

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