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 的性能。
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
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
posted on 2009-05-15 16:17 smwikipedia 阅读(727) 评论(1) 编辑 收藏 举报