I converted some code from vb6 to vb2005 that opens a recordset and populates a listbox with about 8,000 names. It uses classic ado.
The vb6 code does it in about 0.75 of a second, the first vb2005 code does it in about 5.5 seconds while the second vb2005 code does it in about 4.5 seconds. Is there any way to improve the vb2005 performance a fair bit more?
//vb6 code
Dim myconn As ADODB.Connection
Set myconn = New ADODB.Connection
myconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\TestDB1.mdb;Jet OLEDB:System Database=c:\TestDB1.mdw;User ID=TestDB;Password=123456;"
myconn.Open
Dim elap As Double
elap = Timer
List1.Visible = False
List1.Clear
Text1.Text = ""
Text1.Refresh
Dim myrec As New ADODB.Recordset
Dim str1 As String
str1 = "select * from Names"
myrec.Open str1, myconn
myrec.MoveFirst
Do While myrec.EOF <> True
List1.AddItem myrec.Fields("surname").Value & " " & myrec.Fields("firstname").Value
myrec.MoveNext
Loop
List1.Visible = True
Text1.Text = Timer - elap
//
//
//vb2005 code '1st attempt
Dim myconn As New ADODB.Connection
myconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\TestDB1.mdb;Jet OLEDB:System Database=c:\TestDB1.mdw;User ID=TestDB;Password=123456;"
myconn.Open()
Dim elap As Double = DateTime.Now.TimeOfDay.TotalSeconds
list1.Items.Clear()
Text1.Text = ""
Text1.Refresh()
Dim myrec As New ADODB.Recordset
Dim str1 As String = "select * from Names"
myrec.Open(str1, myconn)
myrec.MoveFirst()
list1.BeginUpdate()
Do While Not myrec.EOF
list1.Items.Add(myrec.Fields("surname").Value + " " + myrec.Fields("firstname").Value)
myrec.MoveNext()
Loop
list1.EndUpdate()
Text1.Text = CStr(DateTime.Now.TimeOfDay.TotalSeconds - elap)
//
//
// vb2005 code second attempt
Dim myconn As New ADODB.Connection
myconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\TestDB1.mdb;Jet OLEDB:System Database=c:\TestDB1.mdw;User ID=TestDB;Password=123456;"
myconn.Open()
Dim elap As Double = DateTime.Now.TimeOfDay.TotalSeconds
list1.Items.Clear()
Text1.Text = ""
Text1.Refresh()
Dim myrec As New ADODB.Recordset
Dim str1 As String = "select * from Names"
myrec.Open(str1, myconn)
Dim counter As Integer = 0
myrec.MoveFirst()
Dim MyList As New List(Of String)
Dim MyRow As String
Do While Not myrec.EOF
MyList.Add(myrec.Fields("surname").Value + " " + myrec.Fields("firstname").Value)
myrec.MoveNext()
Loop
list1.BeginUpdate()
list1.Items.AddRange(MyList.ToArray)
list1.EndUpdate()
Text1.Text = CStr(DateTime.Now.TimeOfDay.TotalSeconds - elap)