I attach a program (TestADONET.zip) that compares ADODB to ADO.NET performance. The program inserts a nr of records to an SQL server table with 3 different methods:
1. ADO: A blank ADODB.Recordset is get and filled with the specified number of records. At the end, the Recordset's method UpdateBatch is called.
2. ADO with XML: A blank ADODB.Recordset is get and filled with the specified number of records. At the end, the Recordset is writtern as XML to a stream, and this stream is passed to the stored procedure sp_TestEmployees_InsertXML. This stored procedure calls sp_xml_preparedocument to create a handle for the newly created XML document, and then passes this handle to the OPENXML SQL statement. The OPENXML SQL statement provides a rowset of the XML document, which can then be used by an insert statement to insert the data to a table.
3. ADO.NET: A typed dataset is filled with the specified number of records. At the end, the SqlDataAdapter's Update method is called. The insert command of the SqlDataAdapter is a normal SQL INSERT statement.
The result is, that ADODB, is faster than ADO.NET, even when called from .NET! The program is written in VB.NET 1.1. There is also a help file included, which explains the test scenarios better, and also provides sql scripts to create the table and stored procedure. The attachment TestDb.zip contains an SQL server backup of the database I used, but any database may be used.
I also attach a screen dump (HelpSd1.jpg), which shows the performance of the three insertion methods. On the horizontal axis are given the nr of records that are inserted, and on the vertical axis are given the average times in millseconds for inserting one record. The dots in blue are for ADODB and the dots in aqua marine are for ADO.NET. The graph demonstrates, that ADODB is quite faster than ADO.NET (for inserting records).
1. Can anyone comment, why ADO.NET is slower than ADODB?
2. Can something be changed in the code, to get ADO.NET as fast as ADODB?
1. ADO: A blank ADODB.Recordset is get and filled with the specified number of records. At the end, the Recordset's method UpdateBatch is called.
2. ADO with XML: A blank ADODB.Recordset is get and filled with the specified number of records. At the end, the Recordset is writtern as XML to a stream, and this stream is passed to the stored procedure sp_TestEmployees_InsertXML. This stored procedure calls sp_xml_preparedocument to create a handle for the newly created XML document, and then passes this handle to the OPENXML SQL statement. The OPENXML SQL statement provides a rowset of the XML document, which can then be used by an insert statement to insert the data to a table.
3. ADO.NET: A typed dataset is filled with the specified number of records. At the end, the SqlDataAdapter's Update method is called. The insert command of the SqlDataAdapter is a normal SQL INSERT statement.
The result is, that ADODB, is faster than ADO.NET, even when called from .NET! The program is written in VB.NET 1.1. There is also a help file included, which explains the test scenarios better, and also provides sql scripts to create the table and stored procedure. The attachment TestDb.zip contains an SQL server backup of the database I used, but any database may be used.
I also attach a screen dump (HelpSd1.jpg), which shows the performance of the three insertion methods. On the horizontal axis are given the nr of records that are inserted, and on the vertical axis are given the average times in millseconds for inserting one record. The dots in blue are for ADODB and the dots in aqua marine are for ADO.NET. The graph demonstrates, that ADODB is quite faster than ADO.NET (for inserting records).
1. Can anyone comment, why ADO.NET is slower than ADODB?
2. Can something be changed in the code, to get ADO.NET as fast as ADODB?
Attachments
Last edited:
