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次測試

  • Test Add DateTime wo OracleDbType Rows=33 Duration=65,022ms
  • Test Add DateTime w/ OracleDbType Rows=33 Duration=41ms
  • Test Ad-Hoc SQL Style Rows=33 Duration=41ms
  • Test Add String wo OracleDbType Rows=33 Duration=33ms
  • Test Add String w/ OracleDbType Rows=33 Duration=34ms

    第3次測試

  • Test Add DateTime wo OracleDbType Rows=33 Duration=70,750ms
  • Test Add DateTime w/ OracleDbType Rows=33 Duration=43ms
  • Test Ad-Hoc SQL Style Rows=33 Duration=36ms
  • Test Add String wo OracleDbType Rows=33 Duration=36ms
  • Test Add String w/ OracleDbType Rows=33 Duration=33ms

    第4次測試

  • Test Add DateTime wo OracleDbType Rows=33 Duration=52,096ms
  • Test Add DateTime w/ OracleDbType Rows=33 Duration=38ms
  • Test Ad-Hoc SQL Style Rows=33 Duration=31ms
  • Test Add String wo OracleDbType Rows=33 Duration=34ms
  • Test Add String w/ OracleDbType Rows=33 Duration=36ms

    • 測試結果指出，只有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使用的技巧。" 謝謝你的意見。

    Post a comment