I am using VB.NET.
I am executing a select query that returns approximately 2500 rows, each of which contains 7 fields.
I am using SqlDataAdapater and populating the dataset with a single table returned by the Select query (from the local database). (I only do a data search once (see below), and I don't even start StopWatch until the data is received)
I repeat this table with a for loop and a loop of an array of data objects.
These objects are nothing more than simple structures for storing each row of data.
Just for fun, I do it all 10 times to get a good understanding of how long this takes ... because my intended use would include returning 250,000 rows instead of 2500.
I need to speed it 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 way to retrieve data from SQL? Select directly into the array?
edit: Results. The following code executes in 4 milliseconds, not 2050 milliseconds, taken in a single iteration of the aforementioned 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. Run a query that returns 250,000 results, and it fills the array in 560 ms using a second set of code. Which is fast.
source
share