0

I'm currently migrating an old application that was built using VB6. The application is mainly used to process Data from an Access Database. I was using the DAO library to do all the work. I'm now migrating it to VB.net (using Visual Studio Express 2010) and starting to use the OleDb library but im facing a problem regarding speed. When i try to open and iterate a large table that is around 7 columns and 25 million rows (the datatypes are mainly doubles) from a network location it takes about 10 minutes. When i was using DAO it took me around 1.5 minutes to open the table and run through all the records. This is a major difference in speed in my application, i was not expecting that.

This is the procedure that im using to open that table:

Public Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
Public Const DataBasePath = "My DataBase Location"
Public BE As OleDbConnection = New OleDbConnection(ConnectionString & DataBasePath)

Public Sub OpenLargeTable
    BE.Open()
    Dim QueryExecute As OleDbCommand, DataSet as OleDbDataReader
    Try
        QueryExecute = New OleDbCommand("Select * From LargeTable Order By Field1, Field2, BE)
        DataSet = QueryExecute.ExecuteReader() : DataSet.Read()
        QueryExecute.Dispose() : QueryExecute = Nothing
    Catch
        'Error Handling
    End Try
End Sub

I need to sort the table, and i was using an SQL statement with an Order By clause. I have found out that without the Order By it is very fast, it's just when i order the data that, in comparison with the DAO, it turns out to be really slow. I have tried to create a Query in the access database that sorts the records and then calling the query on the SQL statement in the OleDbCommand object but that doesn't help. My question is, is the OleDbDataReader the best method/object to open a large sorted dataset?

Thanks

user2198816
  • 1
  • Probably you have to create an index on the columns involved – Steve Mar 22 '13 at 12:06
  • Thanks Steve, i have both fields indexed. If that was a problem then i would expect that performing a similar operation with the DAO library would also be slow and it's relatively much faster. – user2198816 Mar 22 '13 at 14:41
  • Well, I knew that DAO was the fastest, but the difference is significant. You could try ACE instead of JET [(Access Database Engine)](http://www.microsoft.com/en-us/download/details.aspx?id=13255), but don't expect too much, also if it is possible, move the database locally – Steve Mar 22 '13 at 14:46
  • Do you truly need to load 25 million rows? Is it possible to limit the rows that you need to pull back? – APrough Mar 22 '13 at 20:28

0 Answers0