Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100

    Unanswered: VB is so s...l...o...w

    I wrote this code in VB 6.0 for a tab control that we've got; I know it's probably awful, and it runs excruciatingly slowly...so any tips are much appreciated. Thank you.

    In a nutshell, we have five different tabs representing five levels of reporting. The options users choose and the reports available to them are dictated by what tab they're on, and they all have a different list of options (which site, which distributor, etc.) and a different subset of reports available to them.

    .NET is not an option. Not at the moment; we can't get the software and nobody on the team can write VB.NET anyway.

    (The connect string is held in a constant at the top of the module. There are probably unused variables floating around in there that I haven't tidied away yet. So sloppy.)

    Code:
    Const strconnString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties='DSN=SQLSERVERNAME-DBNAME;UID=DBUser;APP=Visual Basic;DATABASE=DBNAME;PWD=password'"
    
    Public Sub LoadTabInfo()
    
    Dim rstRec As ADODB.Recordset
    Dim strTabIndex As Integer
    Dim strCurrentTab As String
    Dim strSQL As String
    
    strTabIndex = CriteriaSelection.SSTab1.Tab
    
    Set cnConn = New ADODB.Connection
    cnConn.ConnectionString = strconnString
    cnConn.Open
    
    
    
    If Not cnConn Is Nothing Then
        Select Case strTabIndex
            Case Is = 0
                strSQL = "SELECT Code FROM AcceptanceMethods"
                strCurrentTab = "Head of"
            Case Is = 1
                'strSQL = "SELECT vwFEtop20distributors.Supergroup FROM vwFETop20Distributors Union SELECT MinorSuperGroups.SuperGroup FROM MinorSuperGroups"
                strSQL = "select supergroup from minorsupergroups"
                strCurrentTab = "Distributor"
            Case Is = 2
                strSQL = "SELECT * FROM Site"
                strCurrentTab = "Site"
            Case Is = 3
                strSQL = "SELECT * FROM vwFETeamList"
                strCurrentTab = "Team"
            Case Is = 4
                strSQL = "SELECT Code FROM AcceptanceMethods"
                strCurrentTab = "Parent Company"
            Case Else
                strSQL = "SELECT Code FROM AcceptanceMethods"
        End Select
        
    End If
    Set rstRec = New ADODB.Recordset
    rstRec.Open strSQL, cnConn, adOpenStatic
    
    If rstRec Is Nothing Then
        ' do nothing
    End If
    
    Dim j As Integer
    j = rstRec.RecordCount
    
    If Not rstRec Is Nothing Then
        If Not rstRec.BOF And Not rstRec.EOF Then
            Select Case strTabIndex
                Case Is = 0
                    If CriteriaSelection.lstHeadOf.ListCount = 0 Then
                        CriteriaSelection.lstHeadOf.AddItem ("Boss Lady")
                    End If
                Case Is = 1
                    If CriteriaSelection.lstDist.ListCount = 0 Then
                        For i = 1 To j
                            CriteriaSelection.lstDist.AddItem (rstRec.Fields("SuperGroup"))
                            rstRec.MoveNext
                        Next i
                    End If
                Case Is = 2
                    If CriteriaSelection.lstSite.ListCount = 0 Then
                        For i = 1 To j
                            CriteriaSelection.lstSite.AddItem (rstRec.Fields("Site"))
                            rstRec.MoveNext
                        Next i
                    End If
                Case Is = 3
                    If CriteriaSelection.lstTeam.ListCount = 0 Then
                        For i = 1 To j
                            CriteriaSelection.lstTeam.AddItem (rstRec.Fields("Team"))
                            rstRec.MoveNext
                        Next i
                    End If
                Case Is = 4
                    If CriteriaSelection.lstParentCompany.ListCount = 0 Then
                        For i = 1 To j
                            CriteriaSelection.lstParentCompany.AddItem (rstRec.Fields("Code"))
                            rstRec.MoveNext
                        Next i
                    End If
            End Select
        End If
    End If
                     
    End Sub

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Nothing really jumps out at me, but...

    I would try "stepping" through the code, to see if there is one particular line
    that is slow.

    Do some tabs work faster than others?
    Inspiration Through Fermentation

  3. #3
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by RedNeckGeek
    Nothing really jumps out at me, but...

    I would try "stepping" through the code, to see if there is one particular line
    that is slow.

    Do some tabs work faster than others?

    Yeah; the ones pulling across tiny amounts of data (there's one tab, Site, that has only four options) go relatively quickly while the ones pulling down larger amounts of data (Team, for example) run much more slowly. There was one that I was using a Union query on to begin with and that was just a joke...it took it about five minutes to run. The query itself took three seconds on SQL Server!

    I'm new to this, how do you step?

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by disruptivehair
    I'm new to this, how do you step?
    Set a break point at the beginning of your code. Put your cursor on the line
    "strTabIndex = CriteriaSelection.SSTab1.Tab" and hit F9. That should put a red dot next to the line. Go back to your form, and execute the code. It will stop at the breakpoint. Hit F8 to execute the code one line at a time.
    You should be able to find which line is the slowest. My guess is that it's
    "rstRec.Open strSQL, cnConn, adOpenStatic", but I'm not sure why. Your
    queries aren't complex at all.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    What kind of database are you using?

    Are the tables indexed (especially fields used to sort, group, join, or used in a where clause)

    If not an Access databse, it may be beneficial to use server-side cursor location.

    As a side note - a well designed VB program is very close in speed to a well designed C program. Within 10% or so. The reason that VB has developed a reputation for being slow is that many VB coders don't optimize their code, and don't set compiler directives to allow the VB generated machine code to run as effeciently as is possible.
    Last edited by loquin; 09-06-06 at 18:49.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by RedNeckGeek
    Set a break point at the beginning of your code. Put your cursor on the line
    "strTabIndex = CriteriaSelection.SSTab1.Tab" and hit F9. That should put a red dot next to the line. Go back to your form, and execute the code. It will stop at the breakpoint. Hit F8 to execute the code one line at a time.
    You should be able to find which line is the slowest. My guess is that it's
    "rstRec.Open strSQL, cnConn, adOpenStatic", but I'm not sure why. Your
    queries aren't complex at all.
    Thanks RedNeckGeek; I'll give that a whirl, see what happens.

    If it is rstRec.Open strSQL, cnConn, adOpenStatic, do you have any suggestions as to how to speed that up?

  7. #7
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by loquin
    What kind of database are you using?

    Are the tables indexed (especially fields used to sort, group, join, or used in a where clause)
    The main data table is; not every field that is searched on is indexed because that would mean indexing almost every field on the table and I thought that was a bad thing to do.

    If not an Access databse, it may be beneficial to use server-side cursor location.
    Mebbe, if I knew what that was.

    As a side note - a well designed VB program is very close in speed to a well designed C program. Within 10% or so. The reason that VB has developed a reputation for being slow is that many VB coders don't optimize their code, and don't set compiler directives to allow the VB generated machine code to run as effeciently as is possible.
    You're probably right about that. I'm a noob at this, so I don't know how to optimize VB and I don't know how to set any 'compiler directives' either.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Some thoughts:

    Minimise the data you pull down:
    There are no WHERE clauses on any of your data - is it possible to add any?
    You select * a couple of times - are there any unsued columns that you could omit by explicitly naming columns? EDIT - looking at the code the answer is "Yes".

    By default ADO uses server side cursors. This wouldn't help anyway though as all the data is marshalled to the client in this procedure.

    Instead of looping through the recordsets you can assign the contents using the ADO method GetString. This is a snippet I've just quickly written for a listbox:
    Code:
    Private Sub Form_Open(Cancel As Integer)
     
        Dim adoR As ADODB.Recordset
        Dim sList As String
     
        Set adoR = GetADORecSet
     
        sList = adoR.GetString(adClipString, , ";", ";")
     
        Me.List1.RowSource = sList
     
        adoR.Close
        Set adoR = Nothing
     
    End Sub
    If you do loop then use Do While Not MyRST.EOF rather than recordcount - counting the records and then looping is slower.

    HTH
    Last edited by pootle flump; 09-07-06 at 07:42.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also see below:
    http://msdn.microsoft.com/library/de...tml/sa01l8.asp

    I've used the final idea (Assigning recordsets to controls) for combos. I imagine it would work for listboxes too. You probably couldn't get much more efficient than that. BTW - note you need a client side cursor for that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by pootle flump
    Hi

    Some thoughts:

    Minimise the data you pull down:
    There are no WHERE clauses on any of your data - is it possible to add any?
    You select * a couple of times - are there any unsued columns that you could omit by explicitly naming columns? EDIT - looking at the code the answer is "Yes".

    By default ADO uses server side cursors. This wouldn't help anyway though as all the data is marshalled to the client in this procedure.

    Instead of looping through the recordsets you can assign the contents using the ADO method GetString. This is a snippet I've just quickly written for a listbox:
    Code:
    Private Sub Form_Open(Cancel As Integer)
     
        Dim adoR As ADODB.Recordset
        Dim sList As String
     
        Set adoR = GetADORecSet
     
        sList = adoR.GetString(adClipString, , ";", ";")
     
        Me.List1.RowSource = sList
     
        adoR.Close
        Set adoR = Nothing
     
    End Sub
    If you do loop then use Do While Not MyRST.EOF rather than recordcount - counting the records and then looping is slower.

    HTH

    Thanks Pootle, but I didn't understand most of that. Like I said, I'm a newbie.

    I should add that no, it is not possible to minimize the number of columns coming down. There is only one column on the Site table and only two in vwFETeamList, both of which are needed (though only one is being displayed at the mo). The views/tables/queries contain only precisely what we need for those listboxes...so no, not possible to minimize there.

    I'll try and make sense of the code snippet you sent. I'm not sure how the URL is supposed to relate since we're not using Access here. (I confess I only skimmed it.)
    Last edited by disruptivehair; 09-07-06 at 09:29.

  11. #11
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    OK Pootleflump, the code doesn't work. Error I'm getting is "Method or data member not found" and that's on Me.lstDist.RowSource. That particular attribute isn't showing up on Intellisense, and it doesn't work with DataSource either. Maybe I'm doing something wrong.

    I can get it to sort of work in Access in that it doesn't spray errors but it also doesn't populate the listbox.

    OK, after some tweaking I found something that works in VB:

    CriteriaSelection.lstHeadOf.AddItem (sList)

    But of course, it adds a string delimited by semi-colons and I can't figure out what to do with it next.
    Last edited by disruptivehair; 09-07-06 at 10:13.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - I'll double check my code.

    The article is actually more about controls and ADO than Access. I am pretty sure it relates to VB 6 too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by pootle flump
    Oops - I'll double check my code.

    The article is actually more about controls and ADO than Access. I am pretty sure it relates to VB 6 too.

    The thing is, I can't find a way to put a string into a listbox in VB6 without it showing up as a big long single row. I don't know what property or method of a listbox to use once I have the delimited string. I asked around the office and nobody else knows either.


  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - sorry - there's a good reason for that - my mistake.

    I got mixed up on my IDEs. This has made me realise how ling it is since I last used VB6 - they have all started merging into one. Ho hum. Sorry for wasting your time

    I'm going to check my old programs - I don't remember adding an item at a time at all.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Before you begin adding items into the listbox, set it's .Visible property to False, and restore it to True after you're done adding items. By allowing windows to NOT update the form on each change to the listbox, you can substancially decrease the time required for these updates. (some tests I did showed apx. 40% time reduction to populate a listbox.)

    The fastest recordset to iterate through is a forward-only recordset (as opposed to a static recordset).

    As was earlier suggested, use a Do-While loop, and don't worry about the recordcount. Also note that a recordset will always initially be at the first record, if there is one, and if there are no records in the recordset, .eof will initially be true. So, you can simplify your loop logic

    i.e.
    Code:
    me.lstData.Visible = False
    DoEvents
    Do While not rs.Eof
      Select Case Whatever
        Case 0
          '...
        Case 1
          me.lstView.AddItem rs.Fields(0)
        Case 2
          ' ...
        Case 3
          '...
      End Select
    
      rs.MoveNext
    Loop
    me.lstView.Visible = True
    One last point. Note that referencing a field by it's field number is faster than referencing a field by its name.

    in other words, rs.fields(0) is faster than rs.Fields("SuperGroup") or rs.Fields!Supergroup. By about (per my tests) 10-15 percent.
    Last edited by loquin; 09-08-06 at 12:36.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •