1

為了測試使用System.Data.OracleClient、ODP.NET(Oracle.DataAccess.Client)與TransactionScope三者的效能差距，我設計了以下的實驗，分別用三種方式(因ODP.NET 9207還不支援TransactionScope，所以TransactionScope搭配System.Data.OracleClient使用)將三個Insert動作包成Transaction，並各測十次。

static object obj = new object();
static int _idx;
static int getIdx()
{
    lock (obj)
    {
        _idx++;
        return _idx;
    }
}
 
static void testOraTrn1()
{
    using (OracleConnection cn = new OracleConnection(cnStr))
    {
        cn.Open();
        OracleTransaction trn = cn.BeginTransaction();
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = cn;
        cmd.Transaction = trn;
        cmd.CommandText = "INSERT INTO Jeffrey Values (:EmpNo, :EmpName)";
        OracleParameter pEmpNo = 
            cmd.Parameters.Add("EmpNo", OracleType.Int32);
        OracleParameter pEmpName = 
            cmd.Parameters.Add("EmpName", OracleType.VarChar);
        try
        {
            pEmpNo.Value = getIdx();
            pEmpName.Value = "T1-1st";
            cmd.ExecuteNonQuery();
            pEmpNo.Value = getIdx();
            pEmpName.Value = "T1-2nd";
            cmd.ExecuteNonQuery();
            pEmpNo.Value = getIdx();
            pEmpName.Value = "T1-3rd";
            cmd.ExecuteNonQuery();
            trn.Commit();
        }
        catch
        {
            trn.Rollback();
        }
    }
}
 
static void testOraTrn2()
{
    using (ODP.OracleConnection cn = 
        new Oracle.DataAccess.Client.OracleConnection(cnStr))
    {
        cn.Open();
        ODP.OracleTransaction trn = cn.BeginTransaction();
        ODP.OracleCommand cmd = cn.CreateCommand();
        cmd.Connection = cn;
        cmd.CommandText = "INSERT INTO Jeffrey Values (:EmpNo, :EmpName)";
        ODP.OracleParameter pEmpNo = 
            cmd.Parameters.Add("EmpNo", ODP.OracleDbType.Int32);
        ODP.OracleParameter pEmpName = 
            cmd.Parameters.Add("EmpName", ODP.OracleDbType.Varchar2);
        try
        {
            pEmpNo.Value = getIdx();
            pEmpName.Value = "T2-1st";
            cmd.ExecuteNonQuery();
            pEmpNo.Value = getIdx();
            pEmpName.Value = "T2-2nd";
            cmd.ExecuteNonQuery();
            pEmpNo.Value = getIdx();
            pEmpName.Value = "T2-3rd";
            cmd.ExecuteNonQuery();
            trn.Commit();
        }
        catch
        {
            trn.Rollback();
        }        
        
    }
}
 
static void testOraTrn3()
{
    using (TransactionScope tx = new TransactionScope())
    {
        using (OracleConnection cn = new OracleConnection(cnStr))
        {
            cn.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = cn;
            cmd.CommandText = "INSERT INTO Jeffrey Values (:EmpNo, :EmpName)";
            OracleParameter pEmpNo = 
                cmd.Parameters.Add("EmpNo", OracleType.Int32);
            OracleParameter pEmpName = 
                cmd.Parameters.Add("EmpName", OracleType.VarChar);
            try
            {
                pEmpNo.Value = getIdx();
                pEmpName.Value = "T3-1st";
                cmd.ExecuteNonQuery();
                pEmpNo.Value = getIdx();
                pEmpName.Value = "T3-2nd";
                cmd.ExecuteNonQuery();
                pEmpNo.Value = getIdx();
                pEmpName.Value = "T3-3rd";
                cmd.ExecuteNonQuery();
                tx.Complete();
            }
            catch
            {
                //Log error or something
            }
        }
    }
}
 
static void trnTest(int testNo)
{
    int times = 5;
    long total = 0;
    for (int i = 0; i < times; i++)
    {
        Stopwatch sw = new Stopwatch();
        sw.Start();
        switch (testNo)
        {
            case 1:
                testOraTrn1(); break;
            case 2:
                testOraTrn2(); break;
            case 3:
                testOraTrn3(); break;
        }
        sw.Stop();
        Console.WriteLine("TEST{0}-{1:00} {2}ms",
            testNo, i, sw.ElapsedMilliseconds);
        total += sw.ElapsedMilliseconds;            
    }
    Console.WriteLine("TEST{0} Avg = {1}ms",
        testNo, total / times);
}

測試結果出籠:

TEST1-00 350ms
TEST1-01 5ms
TEST1-02 4ms
TEST1-03 5ms
TEST1-04 4ms
TEST1 Avg = 73ms
TEST2-00 281ms
TEST2-01 7ms
TEST2-02 4ms
TEST2-03 4ms
TEST2-04 5ms
TEST2 Avg = 60ms
TEST3-00 1054ms
TEST3-01 8ms
TEST3-02 9ms
TEST3-03 8ms
TEST3-04 8ms
TEST3 Avg = 217ms

賽前預測，TransactionScope會是最慢的，實測結果也是如此。

不過由數字來看，三者都慢在第一次，推論應是第一次建立Connection較花時間，但之後Connection Pooling發威，速度就變快許多。

我的結論是，TransactionScope雖然方便且具備一些不可取代的涵蓋性(例如: 異質資料庫間的交易、包入3rdParty元件內的資料庫更新等)，但分散式交易在效能上輸給單一資料庫的Transaction實作一大截，在講究效能的場合，要特別留意。


Comments

# by Ark

System.Data.OracleClient 9208 在分散式(IsolationLevel.ReadCommitted)與非分散式交叉使用時會遇到 不定時的ORA-01453 暫推斷為conntion pool機制 和isolation level 卡卡..造成 removing isolation level?clear pool?

Post a comment