oracle入库速度测试

 

测试环境：

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方式

先来个拼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();    }}

­

测试结果：

Count:10240 Time:21828.125

总结：

平均入库速度每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();    }}

­

测试结果：

Count:30720 Time:31609.375

总结：

平均入库速度每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();    }}

­

测试结果：

Count:1024000 Time:26781.25

总结：

平均入库速度每100万条26.40秒

数组绑定法的注意事项：数组长度最好要小于65535，否则……

主意：在添加oracleparameter参数时，顺序要于sql字符串中的参数一致