.NET - SQL Select --> Array. What is the fastest way?

Question

I'm using VB.NET.

I am performing a select query that returns approximately 2500 rows, each containing 7 fields.

I am using a SqlDataAdapater and filling a dataset with the single table returned by the Select query (from the local database). (I only perform the data-retrieval once (see below) and I don't even start the StopWatch until after the data has arrived)

I am iterating through that table with a for-loop and populating an array of objects with data.

These objects are nothing more than simple structures to store each row of data.

Just for fun, I'm doing this all 10 times to get a good feel for how long this is taking... because my desired usage will involve returning 250,000 rows instead of 2,500.

I need to speed this up.

Dim SW As New Stopwatch
SW.Start()
For j As Integer = 0 To 10
    Dim T As DataTable = ds.Tables(0)
    Dim BigArray(0 To T.Rows.Count - 1) As MyObj
    For i As Integer = 0 To T.Rows.Count - 1
        BigArray(i) = New MyObj
        BigArray(i).A = T(i)(0)
        BigArray(i).B = T(i)(1)
        BigArray(i).C = T(i)(2)
        BigArray(i).D = T(i)(3)
        BigArray(i).E = T(i)(4)
        BigArray(i).F = T(i)(5)
        BigArray(i).G = T(i)(6)
    Next
Next
MsgBox(SW.ElapsedMilliseconds)

Any ideas on the fastest method to get data from a SQL Select directly into an array?

edit: Results: The following code executes in 4 milliseconds as opposed to 2050 milliseconds taken by one single iteration of the above outer-loop.

cmd = New SqlCommand("select stuff", conn)
reader = cmd.ExecuteReader()
Dim SW As New Stopwatch
SW.Start()       
Dim BigArray(0 To RowCount - 1) As MyObj
Dim i As Integer = 0
While (reader.Read())

                BigArray(i) = New MyObj
                BigArray(i).A= reader(0)
                BigArray(i).B= reader(1)
                BigArray(i).C= reader(2)
                BigArray(i).D= reader(3)
                BigArray(i).E= reader(4)
                BigArray(i).F= reader(5)
                BigArray(i).G= reader(6)
                i += 1
End While   
MsgBox(SW.ElapsedMilliseconds)

Edit2: FYI - Ran a query returning 250,000 results and it populates the Array in 560ms using the second set of code. That's fast.

Answer 1

Don't go through the Data Table. Use a SqlReader to read each row one at a time, create the object and populate it. SqlCommand.ExecuteReader should get you started.