Thursday, February 12, 2009 - 文章

【茶包射手專欄】又是Parameters.Add闖的禍

同事用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。

Posted 12 February 2009 07:00 PM 由 Jeffrey | 2 comment(s) Filed under: ORACLE, ASP.NET, Trouble-Shooting

Mini jQuery Lab

邊做邊學jQuery 3,4,5集已經上線了，有興趣的人可以連上去看看:

jQuery 常用的Javascript 技巧

神奇的 jQuery Selector

jQuery 的樣式、屬性、欄位內容存取語法

有看Demo影片的人應該會發現我寫了一個好東西(至少我自己覺得很好用啦!)--Mini jQuery Lab!

在我學習測試jQuery的過程，常需要寫個一兩行測一下某個函數或某段Javascript寫法是否OK。在這種情況下，即便是在Visual Studio 2008中，也要歷經寫Code、存檔、View In Browser的過程，對一般人來說已經很方便，但對懶惰又性急的我來說總覺得太繁瑣太緩慢了，需要是發明之母，所以我又做了潛盾機出來，這跟當年開發Mini C# Lab的心路歷程差不多。(謎之聲: 很少看到這種一路走來，始終如一的懶人...)

介面分成三區，可以分別輸入CSS、Body HTML以及Script，Script的部分會被自動包在$(function() {  });中，而jQuery 1.3.1已載入，$給它直接用下去就對了。修改完成按下Execute直接看結果，很爽快吧!

上方有個View HTML Source的按鈕，按下可以看到HTML內容，透過SyntaxHighlighter可以將HTML輕鬆Copy出來將結果另存檔案留念。

我難得有工具還提供教學錄影實際操作示範，這裡就不多廢話了。

最後一定要提一點，jQuery真的夠簡潔! MiniJQueryLab.htm的全部功能，HTML含Script不到150行就打死，比我原先預期的還少，不虧是省Code一哥。

Posted 12 February 2009 05:51 AM 由 Jeffrey | 7 comment(s) Filed under: Tools, 潛盾機, jQuery

搜尋