【茶包射手專欄】又是Parameters.Add闖的禍
2 |
同事用ODP.NET跑一段SQL,得到驚人的結果:
select .... from
( select .... from TABLE_1 where someDate = :pDate and .... ) p
join
( select .... from TABLE_2 where someDate = :pDate and .... group by ...
union
select ... from TABLE_3 where someDate = :pDate and ... group by ...) c
on p.col1 = c.col1 and p.col2 = c.col2 and p.col3 = c.col3
where p.col4 <> c.col4
中間只用了一個Parameter :pDate,看似並不複雜的查詢,居然耗時要一分鐘以上才會得到結果。暴怒之餘,從ODP.NET 9207改用System.Data.OracleClient,猜怎麼著? 不用一秒就搞定!
接獲ODP.NET又跑出來撤野的線報,我像鯊魚嗅到血腥味一樣又興奮了起來... (謎之聲: 你跟ODP.NET有不共戴天之仇哦?)
仔細看了程式,我發現原程式用了cmd.Parameters.Add("pDate", date)這種自動判別資料型別的寫法,之前我吃過一次自動型別判斷的虧,於是試著改成cmd.Parameters.Add("pDate", OracleDbType.Date).Value = date,沒想到馬上跟閃雷一樣,瞬間得到結果。
跟其他同事討論,有人貢獻傳入string再做TO_DATE的心得,我索性整理一下,來做做個各式方法的效能評比。
private void TestODPClient(string cnStr, string cmdText, DateTime date)
{
ODP.OracleCommand cmd = new ODP.OracleCommand(cmdText);
cmd.Parameters.Add("pDate", date);
RunTest(@"Add DateTime wo OracleDbType", cmd, cnStr);
cmd = new ODP.OracleCommand(cmdText);
cmd.Parameters.Add("pDate", ODP.OracleDbType.Date).Value = date;
RunTest(@"Add DateTime w/ OracleDbType", cmd, cnStr);
cmd = new ODP.OracleCommand(cmdText);
cmd.CommandText = cmd.CommandText.Replace(":pDate",
"TO_DATE('" + date.ToString("yyyyMMdd") + "', 'YYYYMMDD')");
RunTest("Ad-Hoc SQL Style", cmd, cnStr);
cmd = new ODP.OracleCommand(cmdText);
cmd.CommandText = cmd.CommandText.Replace(":pDate",
"TO_DATE(:pDateStr, 'YYYYMMDD')");
cmd.Parameters.Add("pDateStr", date.ToString("yyyyMMdd"));
RunTest(@"Add String wo OracleDbType", cmd, cnStr);
cmd = new ODP.OracleCommand(cmdText);
cmd.CommandText = cmd.CommandText.Replace(":pDate",
"TO_DATE(:pDateStr, 'YYYYMMDD')");
cmd.Parameters.Add("pDateStr", ODP.OracleDbType.Varchar2).Value =
date.ToString("yyyyMMdd");
RunTest(@"Add String w/ OracleDbType", cmd, cnStr);
}
private void RunTest(string testName, ODP.OracleCommand cmd, string cnStr)
{
using (ODP.OracleConnection cn = new
Oracle.DataAccess.Client.OracleConnection(cnStr))
{
cmd.Connection = cn;
DataTable dt = new DataTable();
cn.Open();
Stopwatch sw = new Stopwatch();
sw.Start();
ODP.OracleDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
sw.Stop();
cn.Close();
Response.Write(
string.Format("<li>Test {0} Rows={1} Duration={2:#,0}ms",
testName, dt.Rows.Count, sw.ElapsedMilliseconds));
}
}
第1次測試
- Test Add DateTime wo OracleDbType Rows=33 Duration=81,124ms
- Test Add DateTime w/ OracleDbType Rows=33 Duration=3,090ms
- Test Ad-Hoc SQL Style Rows=33 Duration=42ms
- Test Add String wo OracleDbType Rows=33 Duration=36ms
- Test Add String w/ OracleDbType Rows=33 Duration=36ms
第3次測試
第3次測試
第4次測試
測試結果指出,只有Add("pDate", date)的時間異常,是其他方法的數千倍,一樣沒指定資料型別的Add("pDateStr", date.ToString("yyyyMMdd"))速度卻正常。
由此推論,會出問題的只有Add("paramName", dateTypeValue)。不過,如果是我,我會乖乖加上OracleDbType參數。不不不,如果不是被刀架著脖子、被槍指著頭,我應該會用System.Data.OracleClient。
Comments
# by 睡貓
最好要指定類行,不然不一定會走到建立的INDEX。可以使用toad,看plan就知道問題在那了
# by Jeffrey
to 睡貓,是指在ODP.NET層次的Parameter指定類型與否會影響Execution Plan嗎? 我一直用MS SQL的思維去理解Parameter轉成SQL語言的過程,以為會在轉化Paramter的過程中,就透過偵測物件型別的技巧都自動轉成某種DB型別了。 聽起來,Oracle比我想得奧妙多了,對ORACLE我的了解有限,方便的話,希望可以再分享一下用Toad偵察指定型類影響INDEX使用的技巧。" 謝謝你的意見。