views:

443

answers:

1

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)
+1  A: 

The original COM based ADO components are not supported for use within .NET use ADO.NET components instead.

BTW, Do your users find having 8000 items to choose from in a List box particulary helpful and easy to use?

AnthonyWJones
Well they are supported actually or else the thing wouldn't work at all. I'm investigating a migration and as little rewriting of the logic as poss is desirable from a pragmatic povAlso sometimes users can't be bothered to enter any filtering criteria and then rather than nanny them I give them the full list, after all, it only takes an instant to dish it up.
kjack
It seems ADO in VB.NET doesn't always work, for example see this http://www.vbmigration.com/detknowledgebase.aspx?Id=100 KJack, talking of pragmatism, have you looked at the automatic migration tools from Artinsoft and VBMigration.com?
MarkJ
Hi MarkJYes I have looked at artinsoft and vbmigrationpartner. I have experimented with artinsoft's developeredition trial and was really impressed.Some simple programs that used both dao and ado tointeract with access databases convertedflawlessly.I would say that I will probably ultimately purchaseartinsoft's enterprise edition but in an effortto learn vb.net and maybe reduce the cost of the conversionI am looking at doing some of it myself. Artinsoft add in design lines in calculating costswhich bumps up the price a bit.
kjack
BTW vbmigration partner add in variable declarations etc.If you use either artinsoft's or vbmigration partner's assessment tools you'll get a much larger LOC than, say, MZ tools reports. For artinsoft mine was increased by almost 50%.
kjack
BTW (2) Artinsoft's vbuc did a conversion of the above code but added a reference to an artinsoft dll which contains an artinsoft.GUI.helperclass. Consequently the artinsoft conversion worked as fast as vb6.AFAIK the enterprise edition would result in code that does not require a support library but it's quite a bit dearer.
kjack