ADO.NET SQL Provider 和 ADO.NET OLE DB Provider 和 ADO.NET ODBC Provider 性能对比。

 

通过向数据库中反复执行插入指令，比较ADO.NET OLE DB Provider ， ADO.NET SQL Provider 和ADO.NET ODBC Provider 访问 SQL Server 的性能。

 

 

namespace OLEvsADONET
{
    class Program
    {
        private static int MAX = 200;
        static void Main(string[] args)
        {
            
            DateTime start, end;

            start = DateTime.Now;
            ADONET();
            end = DateTime.Now;
            long cost1 = end.Ticks - start.Ticks;
            Console.WriteLine("ADO.NET cost {0} ticks.", cost1);

            start = DateTime.Now;
            OLE();
            end = DateTime.Now;
            long cost2 = end.Ticks - start.Ticks;
            Console.WriteLine("OLE DB  cost {0} ticks.", cost2);

            start = DateTime.Now;
            ODBC();
            end = DateTime.Now;
            long cost3 = end.Ticks - start.Ticks;
            Console.WriteLine("ODBC    cost {0} ticks.", cost3);

        }

        private static void OLE()
        {
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = "Provider=SQLOLEDB;Data Source=.;Integrated Security=SSPI;Initial Catalog=mydb";
            conn.Open();
            OleDbCommand olecmd = conn.CreateCommand();
            olecmd.CommandType = CommandType.Text;
            olecmd.CommandText = "use mydb;insert into Table_1 (id, name) values ('1','sam')";
            for (int i = 0; i < MAX; i++)
                olecmd.ExecuteNonQuery();
        }

        private static void ADONET()
        {
            SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder();
            connBuilder.DataSource = ".";
            connBuilder.InitialCatalog = "mydb";
            connBuilder.IntegratedSecurity = true;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connBuilder.ToString();
            conn.Open();
            SqlCommand sqlcmd = conn.CreateCommand();
            sqlcmd.CommandType = CommandType.Text;
            sqlcmd.CommandText = "use mydb;insert into Table_1 (id, name) values ('1','sam')";
            for (int i = 0; i < MAX; i++)
                sqlcmd.ExecuteNonQuery();
        }

        private static void ODBC()
        {
            OdbcConnectionStringBuilder connBuilder = new OdbcConnectionStringBuilder();
            connBuilder.Dsn = "mysqlserver";
            OdbcConnection conn = new OdbcConnection();
            conn.ConnectionString = connBuilder.ToString();
            conn.Open();
            OdbcCommand olecmd = conn.CreateCommand();
            olecmd.CommandType = CommandType.Text;
            olecmd.CommandText = "use mydb;insert into Table_1 (id, name) values ('1','sam')";
            for (int i = 0; i < MAX; i++)
                olecmd.ExecuteNonQuery();
        }

    }
}

 

输出如下：

MAX=50

ADO.NET cost 1740174 ticks.
OLE DB   cost 810081 ticks.
ODBC     cost 450045 ticks.

MAX=1000

ADO.NET cost 6060606 ticks.
OLE DB   cost 7160716 ticks.
ODBC     cost 4970497 ticks.

MAX=10000

ADO.NET cost 59245924 ticks.
OLE DB   cost 76817681 ticks.
ODBC     cost 55645564 ticks..

 

MAX=30000

ADO.NET cost 179757974 ticks.
OLE DB  cost 237743772 ticks.
ODBC    cost 186518650 ticks.

 

MAX=50000

ADO.NET cost 293179315 ticks.
OLE DB  cost 432073203 ticks.
ODBC    cost 322262223 ticks.

 

结论：

当操作数据较小时，采用OLE DB Provider 或 ODBC Provider 的 Insert 操作性能优于 SQL Provider。

而当操作数据较多时，采用 SQL Provider 更好。

对于其他操作，如 Update, Delete, Create 并没有测试。

 

附: MDAC Framework