CODEWITHSUNDEEP
.netsqlvb.netarrays
Brian Webster
Brian Webster

Reputation: 30855

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

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.

Upvotes: 5

Views: 4427

Answers (2)

Dovix
Dovix

Reputation: 65

Use a DataReader instead of a DataTable -> Array. Using the data reader you can write the values directly into the array. I don't think it gets faster that that.

Upvotes: 3

popester
popester

Reputation: 1934

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.

Upvotes: 4

Related Questions