none
Slow database access in VB .Net RRS feed

  • Question

  • I've got a MS Access 2000 database with a little over 6 million records.  Using some VBA/DAO code in a module within the database, I can loop through those records (doing some minor processing / no writebacks) in about 17 seconds.  I can get almost the same performance using VBA/DAO in an Excel project.  But when I code the same thing up in VB .NET, it takes about 30 seconds to open up the recordset, and then another 130 seconds to loop through the data???

    I then rewrote it using using an ADO .NET OleDbDataReader.  Things were much better : 30 seconds for the ExecuteReader command, and 22 seconds to loop through the data.  Big improvement, but still way slower than the first two methods.

    Why is it so much slower in .Net?  Is it the overhead of the CLR?  Is there any way I can make it run faster in .Net?  (My project needs to be in VB .Net.)

     

    Tuesday, February 9, 2010 7:30 PM

All replies

  • Maybe I should post some code.  Following is the ADO version.

        Sub SpeedTestADO()
            Dim rdr As OleDbDataReader
            Dim CurrentPrice As Double
            Dim Volume As Integer
            Dim dt As DateTime
            Dim sql As String
            Dim mCon As OleDbConnection
            Dim dbPath As String
    
    
            sql = "SELECT * FROM Data WHERE Tickdate>=#8/1/2009 00:00# AND Tickdate<=#1/31/2010 23:59# ORDER BY TickDate"
            dbPath = "C:\Projects\TWS VBClient\Databases\QM Futures 1 sec Dec 08+.mdb"
            mCon = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath)
            Dim mCmd As New OleDbCommand(sql, mCon)
            mCon.Open()
            rdr = mCmd.ExecuteReader(CommandBehavior.CloseConnection)
            While rdr.Read
                dt = rdr("Tickdate")
                CurrentPrice = rdr("Close")
                Volume = rdr("Volume")
            End While
            rdr.Close()
        End Sub
    
    
    Tuesday, February 9, 2010 8:52 PM