none
Speed compare : ADODB vs ADO.NET vs Microsoft Access

    Question

  • Let try this two sub :

    '================

    Public Sub TestADODB()

       Dim rs As New ADODB.Recordset
            Dim strTemp As String
            Dim i As Integer
            Dim conn As New ADODB.Connection
           conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "D:\Data.mdb" & ";")

            oWatch.Start()
            rs.Open("Select * from tblEmail", conn, 3, ADODB.LockTypeEnum.adLockReadOnly)
            Dim oWatch As New Stopwatch

        
            For i = 1 To 120000
                rs.MoveNext()
                strTemp = rs("email").Value
            Next

            oWatch.Stop()

            MsgBox(oWatch.ElapsedMilliseconds.ToString)

    End sub

      Public Sub TestADONet()
            Dim conn As OleDbConnection = Nothing
            Dim reader As OleDbDataReader = Nothing
            Dim strTemp As String
            conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source=" + "D:\Data.mdb")
            conn.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * from tblEmail", conn)
            Dim oWatch As New Stopwatch
            oWatch.Start()
            reader = cmd.ExecuteReader
            For i = 1 To 120000
                If (reader.Read()) Then
                    strTemp = reader.GetValue(2).ToString
                End If
            Next
            oWatch.Stop()

            MsgBox(oWatch.ElapsedMilliseconds.ToString)
        End Sub

    '================

    I've wondered that ADO.NET is recommend so it could be faster, but when try a testing between two function above. I found that the sub TestADODB take me only 2800ms when the TestADONet take me 4600ms. So i don't know which is better ?

    When i open that .mdb file with Access 2007, it's take me only about 0,5 second to load that table without earning over 40% CPU.  When i try to using ADODB with 2 thread, it take me 1500 ms with 100% CPU earning. So what's the different with the technology of Microsoft Access with the ADODB in my application and can i do the same performance in my application ?







    • Edited by vuadapass Sunday, September 02, 2012 1:17 AM
    Saturday, September 01, 2012 4:36 PM

Answers

  • It's all a matter of design.

    First, ADO was not really designed for Microsoft Access. It's a universal database access library that supports any database that has an OLEDB Provider. Second, I would expect even more overhead when using the .NET OLEDB library and ADO.NET.

    Third, Access has it's own internal database engine, which will be faster than the external OLEDB Provider library. With respect to application development, the DAO library, which is specifically designed for Access databases, will be also be faster than ADO/OLEDB.

    Keep in mind though that if you are developing your app in .NET, ADO.NET is designed to integrate with .NET technologies, where both Classic ADO and DAO are not.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, September 04, 2012 12:51 PM

All replies