Fastest way to fill Structure from database

[Norkis]

Hey,

I have structure and database.mdb with one table and columns with same name as in structure. What is the fastest way to fill structure with date from database? My database consists more than 200000 records, so it must be fastest way.

My structure:

Public Structure Balas
        Public Prioritetas As Int16
        Public Kodas As Int16
        Public Balas As Double
    End Structure

My code to load data:

Public Sub LoadArrays()
        Dim Balas_() As Balas
        Dim n As Long
        Dim strDSN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\studijos.mdb"
        Dim strSQL As String = "SELECT * FROM Studijos"
        Dim myConn As New OleDbConnection(strDSN)
        Dim myCmd As New OleDbCommand(strSQL, myConn)
        Dim datareader As OleDbDataReader = Nothing
        n = 0
        myCmd.CommandText = "SELECT * FROM Balai"
        myConn.Open()
        datareader = myCmd.ExecuteReader()
        While datareader.Read()
            ReDim Preserve Balas_(n)
            Balas_(n).Balas = datareader("Balas")
            Balas_(n).Kodas = datareader("Kodas")
            Balas_(n).Prioritetas = datareader("Prioritetas")
            n = n + 1
            DoEvents()
            End While

        myConn.Close()
    End Sub

Is faster way?

[jovton]

yep i think so. it depends on what you mean. If you load real data into a structure, list or whatever, the "connected" mode you're using can be quite fast for the task, if the query result set is not extreme large.

If you need to fill a only the table structure though, the DataSet object has a "FillSchema" method that does the job nicely. And if the result set is quite large, the "fill" method will do the trick better than a DataReader, I think. Comments from other users are welcome. THIS, however, is "disconnected" mode, and has its uses, advantages and disadvantages. Too much detail to expand here. Stay tuned...

[jovton]

The following code does the same job, but in "disconnected" mode (which I think might be faster for 200000 records):

Public Sub LoadArrays()
        Dim Balas_() As Balas
        Dim n As Integer
        Dim strDSN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\studijos.mdb"
        Dim strSQL As String = "SELECT * FROM Balai"
        Dim myConn As New OleDbConnection(strDSN)
        Try
 
            n = 0
            If myConn.State = ConnectionState.Closed Then
                myConn.Open()
            End If
 
            Dim myDa As New OleDbDataAdapter(strSQL, myConn)
            Dim myDt As New DataTable, myDr As DataRow
 
            myDa.Fill(myDt)
 
            For Each myDr In myDt.Rows
                ReDim Preserve Balas_(n)
                Balas_(n).Balas = CType(myDr.Item("Balas"), Double)
                Balas_(n).Kodas = CType(myDr.Item("Kodas"), Short)
                Balas_(n).Prioritetas = CType(myDr.Item("Prioritetas"), Short)
                n = n + 1
            Next myDr
 
            ' blah blah blah
 
        Catch ex As OleDbException
            MessageBox.Show("Error " & ex.ErrorCode & ": " & ex.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
 
        Finally
            myConn.Close()
        End Try
 
    End Sub

[Norkis]

Thanx for your help jovton.

I think it work a little bit faster.

But now I think I have other problem .
When I fill structure with data it takes a lot of computer memory.

How to solve this?

Should I work by reading data directly from data base?

[jovton]

Hmmm, yes. Two versions of 200000 records in memory is a lot. You'll have to compromise between speed, and memory. Reading each row directly from the database using a DataReader, will probably be slower, but it will use much less memory.

On the other hand, if you are confident that most users will have amount of memory you have right now, you can try and release the resources used by the DataTable and the DataSet by "disposing" them (object.dispose) and then point them to Nothing. After that, manually call the "GC.Collect(True)" method to take out the garbage.

This will cause the huge amount of memory to be consumed only for a short while (while the data is being read).

I'm not sure if this will help. But you can try it, and tell me if it works for you.

Other comments welcome.

[jovton]

I just had another idea... If you're going to use an array, why not just use the DataTable instead? It might take more memory than an array, but definitely much less than both an Array AND a DataTable.

You can sort the rows by using "SELECT * FROM Balai ORDER BY Balas". Sorting an array will take longer and more effort. You can also be very selective about which columns (fields) you want in your datatable, which might also spare some memory (e.g. "SELECT Balas, Kodas, Prioritetas FROM Balai ORDER BY Balas").