Performance Comparison Between ADO. Net SQL provider and ADO. Net OLE DB provider and ADO. Net ODBC provider.

 

By repeatedly executing the INSERT command into the database, compare the performance of accessing SQL Server by ADO. Net ole db provider, ADO. Net SQL provider and ADO. Net ODBC provider.

 

 

{
Function onclick ()
{
Function onclick ()
{
This. style. display = 'none'; document. getelementbyid ('Code _ closed_text_163726 '). style. display = 'none'; document. getelementbyid ('Code _ open_image_163726 '). style. display = 'inline'; document. getelementbyid ('Code _ open_text_163726 '). style. display = 'inline ';
}
}
} "Id =" code_closed_image_163726 "style =" display: none "> {
Function onclick ()
{
Function onclick ()
{
This. style. display = 'none'; document. getelementbyid ('Code _ open_text_163726 '). style. display = 'none'; getelementbyid ('Code _ closed_image_163726 '). style. display = 'inline'; getelementbyid ('Code _ closed_text_163726 '). style. display = 'inline ';
}
}
} "Id =" code_open_image_163726 "> code
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 ();
}

}
}

 

The output is as follows:

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.

 

Conclusion:

When the operation data is small, the insert operation using ole db provider or ODBC provider has better performance than SQL provider.

When there is a large amount of operation data, it is better to use SQL provider.

Other operations, such as update, delete, and create, are not tested.

 

Appendix: MDAC framework