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 ?

Answer 1

Hello,

I would consider more than simply speed to decide which way to access data. The following link might assist with that in mind.

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/1a9e3670-9685-4943-913b-123ecf248a9c/

A major point to use OleDb over ADODB is if you will move to a 64 bit OS as per the following thread.

http://social.msdn.microsoft.com/Forums/sa/adodotnetdataproviders/thread/00c2a790-a07c-462f-b577-e51e403c0fc1

---

KSG

Answer 2

You should not be surprised that different queries (SELECT Email... vs SELECT *...) take different times.

--
Andrew

Answer 3

Dear Andrew Morton, all queries edited to "Select * from tblEmail" and still no different.

The biggest question now is what's the different technology of Microsoft Access with the ADODB in my application and can i do the same performance in my application ?

Answer 4

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)

Answer 5

Did you try this already with 1000 end users active?

AdoDB is created in the time 50 end users was very much, those times are over.

AdoDb is an connected way, while AdoNet is based around disconnected access, therefore your question is apples and computers.. 

---

Success
Cor