Oracle入库速度测试(C#版)

最新推荐文章于 2021-04-05 08:07:24 发布

最新推荐文章于 2021-04-05 08:07:24 发布 · 256 阅读

文章标签：

#C #C++ #C# #Oracle #SQL Server

c# 专栏收录该内容

2 篇文章

订阅专栏

本文通过三种不同方法对比了在Oracle数据库中进行批量数据插入的性能表现：直接拼接SQL语句、使用绑定参数和使用数组绑定参数。结果显示，数组绑定参数的方法性能最优。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

测试环境：
Intel Xeon 2.4G四核心 2.5G内存
Server 2003 Enterprise Edition Service Pack 2
Oracle9i Enterprise Edition 9.2.0.1.0
Microsoft Visual Studio 2008
组件Oracle提供的Oracle.DataAccess.dll
建立测试表：

CREATE TABLE TEST
(
TEST_ID NUMBER(10, 0),
TEST_NAME VARCHAR2(50),
TEST_TIME TIMESTAMP,
TEST_VALUE NUMBER(10, 3)
);

连接Oracle过程略。

一个测试用的类：

class Util
{
    public static Random rand = new Random();
    public static String atoz = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
    public static String GenString(int length)
    {
        StringBuilder re = new StringBuilder(length);
        for (Int32 i = 0; i < length; i++)
        {
            re.Append(atoz[rand.Next(52)]);
        }
        return re.ToString();
    }
    public static Double GenDouble()
    {
        Double d1 = 2500 * rand.NextDouble();
        Double d2 = 500000 * rand.NextDouble();
        return d1 + d2;
    }
    public static Int32[] Split(Int32 total)
    {
        Int32 splitSize = 51200;
        Int32[] result = new Int32[(total + splitSize - 1) / splitSize];
        Int32 idx = 0;
        for (; idx < result.Length - 1; idx++)
        {
            total = total - splitSize;
            result[idx] = splitSize;
        }
        result[idx] = total;
        return result;
    }
}

先来个拼sql入库：

public static void SimpleInsert(Int32 total)
{
    Thread.Sleep(new TimeSpan(0, 0, 3));
    DateTime current = DateTime.Now;
    String currentStr = current.ToString("yyyy-MM-dd HH:mm:ss");
    using (OracleConnection oraConn = new OracleConnection(connStr))
    {
        oraConn.Open();
        DateTime begin = DateTime.Now;

        for (Int32 i = 1; i <= total; i++)
        {
            String sqlStr = String.Format("INSERT INTO TEST (TEST_ID, TEST_NAME, TEST_TIME, TEST_VALUE) VALUES ({0}, '{1}', to_date('{2}','yyyy-MM-dd HH24:mi:ss'), {3})", (Int32)i, Util.GenString(5), currentStr, new Decimal(Util.GenDouble()));
            OracleCommand oraCmd = new OracleCommand(sqlStr, oraConn);
            oraCmd.ExecuteNonQuery();
        }

        DateTime end = DateTime.Now;
        Console.WriteLine("Count:{0} Time:{1}", total, (end - begin).TotalMilliseconds);
        oraConn.Close();
    }
}

测试结果：
[i]Count:10240 Time:21828.125
Count:10240 Time:21890.625
Count:10240 Time:23234.375
Count:10240 Time:30468.75
Count:10240 Time:22734.375
Count:10240 Time:22484.375
Count:10240 Time:22593.75
Count:10240 Time:24203.125
Count:10240 Time:22546.875
Count:10240 Time:22375[/i]
总结：
平均入库速度每1万条22.89秒

绑定参数法入库：

public static void TraditionalInsert(Int32 total)
{
    Thread.Sleep(new TimeSpan(0, 0, 3));
    DateTime current = DateTime.Now;
    using (OracleConnection oraConn = new OracleConnection(connStr))
    {
        oraConn.Open();
        DateTime begin = DateTime.Now;

        OracleCommand oraCmd = new OracleCommand("INSERT INTO TEST (TEST_ID, TEST_NAME, TEST_TIME, TEST_VALUE) VALUES (:t_id, :t_name, :t_time, :t_value)", oraConn);
        OracleTransaction transaction = oraConn.BeginTransaction(IsolationLevel.ReadCommitted);
        OracleParameter tId = new OracleParameter("t_id", OracleDbType.Decimal);//, 4, "TEST_ID");
        oraCmd.Parameters.Add(tId);
        OracleParameter tName = new OracleParameter("t_name", OracleDbType.Varchar2);//, 100, "TEST_NAME");
        oraCmd.Parameters.Add(tName);
        OracleParameter tTime = new OracleParameter("t_time", OracleDbType.TimeStamp);//, 8, "TEST_TIME");
        oraCmd.Parameters.Add(tTime);
        OracleParameter tValue = new OracleParameter("t_value", OracleDbType.Decimal);//, 9, "TEST_VALUE");
        oraCmd.Parameters.Add(tValue);
        for (Int32 i = 1; i <= total; i++)
        {
            tId.Value = new Decimal(i);
            tName.Value = Util.GenString(5);
            tTime.Value = current;
            tValue.Value = new Decimal(Util.GenDouble());
            oraCmd.ExecuteNonQuery();
        }
        transaction.Commit();


        DateTime end = DateTime.Now;
        Console.WriteLine("Count:{0} Time:{1}", total, (end - begin).TotalMilliseconds);
        oraConn.Close();
    }
}

测试结果：
[i]Count:30720 Time:31609.375
Count:30720 Time:31875
Count:30720 Time:31687.5
Count:30720 Time:31187.5
Count:30720 Time:30906.25
Count:30720 Time:31078.125
Count:30720 Time:30781.25
Count:30720 Time:31062.5
Count:30720 Time:31375
Count:30720 Time:32125[/i]
总结：
平均入库速度每1万条10.21秒

数组绑定法入库：

public static void ArrayBindInsert(Int32 total)
{
    Thread.Sleep(new TimeSpan(0, 0, 3));
    DateTime current = DateTime.Now;
    Int32[] splitTotal = Util.Split(total);

    using (OracleConnection oraConn = new OracleConnection(connStr))
    {
        oraConn.Open();
        DateTime begin = DateTime.Now;

        foreach (Int32 smallCout in splitTotal)
        {
            Decimal[] tId = new Decimal[smallCout];
            String[] tName = new String[smallCout];
            DateTime[] tTime = new DateTime[smallCout];
            Decimal[] tValue = new Decimal[smallCout];
            for (Int32 i = 1; i <= smallCout; i++)
            {
                tId[i - 1] = new Decimal(i);
                tName[i - 1] = Util.GenString(5);
                tTime[i - 1] = current;
                tValue[i - 1] = new Decimal(Util.GenDouble());
            }
            OracleCommand oraCmd = new OracleCommand("INSERT INTO TEST (TEST_ID, TEST_NAME, TEST_TIME, TEST_VALUE) VALUES (:t_id, :t_name, :t_time, :t_value)", oraConn);
            oraCmd.ArrayBindCount = smallCout;

            OracleParameter pId = new OracleParameter("t_id", OracleDbType.Decimal);
            pId.Direction = ParameterDirection.Input;
            pId.Value = tId;
            oraCmd.Parameters.Add(pId);

            OracleParameter pName = new OracleParameter("t_name", OracleDbType.Varchar2);
            pName.Direction = ParameterDirection.Input;
            pName.Value = tName;
            oraCmd.Parameters.Add(pName);

            OracleParameter pTime = new OracleParameter("t_time", OracleDbType.TimeStamp);
            pTime.Direction = ParameterDirection.Input;
            pTime.Value = tTime;
            oraCmd.Parameters.Add(pTime);

            OracleParameter pValue = new OracleParameter("t_value", OracleDbType.Decimal);
            pValue.Direction = ParameterDirection.Input;
            pValue.Value = tValue;
            oraCmd.Parameters.Add(pValue);

            oraCmd.ExecuteNonQuery();
        }

        DateTime end = DateTime.Now;
        Console.WriteLine("Count:{0} Time:{1}", total, (end - begin).TotalMilliseconds);
        oraConn.Close();
    }
}

测试结果：
[i]Count:1024000 Time:26781.25
Count:1024000 Time:24203.125
Count:1024000 Time:24375
Count:1024000 Time:26484.375
Count:1024000 Time:24250
Count:1024000 Time:29812.5
Count:1024000 Time:26640.625
Count:1024000 Time:28437.5
Count:1024000 Time:31156.25
Count:1024000 Time:28203.125[/i]
总结：
平均入库速度每100万条26.40秒
数组绑定法的注意事项：数组长度最好要小于65535，否则……