Performance problem - how to analyse?

[AndyKennedy]

hi all

I have a performance issue with a VB6 app connecting to an Access db using vb's Data Environment. Happens with a single user logged on, and in lots of different functions within the app. I have put display statements showing elapsed time to execute particular lines of code in one of these functions, but cant see how to progress ths investigation further. What I have found so far:

The code loops around an array with 37 entries in it populating data in an object for each entry. For the first 33 entries the code executes fine, no performance issues. Then it slows right down when executing the db commands. These are all select statements stored in the Access db as queries, and each use a single parameter containing either the primary key of the table being queried, or a foreign key which is indexed. Loads of memory and CPU available, no significant disk activity apparently going on.

My first post on VB Forums and would be grateful for any help as to how to progress this...!

[CDRIVE]

Welcome to the forum Andy. You may want to consider posting this in the Database Automation section of the forum.

[akhileshbc]

Hi Andy... Welcome to the forums...

I think, it will be even more helpful if you show us the code.

Welcome to the forum Andy. You may want to consider posting this in the Database Automation section of the forum.

Hi CDRIVE...

If you find this post/thread as inappropriate in this forum category, then you can report it to the Mod team by simply clicking on the Exclamation mark icon of that post (which is situated below the Rate this post link), so that the mod team will move (or do whatever operations needed) this thread.

[CDRIVE]

Hi CDRIVE...

If you find this post/thread as inappropriate in this forum category, then you can report it to the Mod team by simply clicking on the Exclamation mark icon

It doesn't bother me at all. It might be beneficial to him though.

[akhileshbc]

It doesn't bother me at all. It might be beneficial to him though.

I thought you are not aware about the "Report Post" facility

[CDRIVE]

I thought you are not aware about the "Report Post" facility

Ha, I may be getting old and the bulb isn't shining quite as brightly but I'm not that far gone... ... Yet!

[dilettante]

You might try a profiler like VB Watch. If your project is small enough ("10 files" which I assume means the VBP too but might only mean module files FRM, CLS, CTL, BAS) you may even be able to use the trial version. If the project is larger you might be able to create a "pared down" version of it that the trial will work on.

This won't help track down problems in slow queries, concurrency deadlocks, etc. but it might shed light on where the slowdowns are actually occurring in the program.


Is it possible you are using multiple Connections in your Data Environment? They might be fighting each other over locks, page buffer lazy writes, or commits. A profiler might help reveal some of these issues.

Is the MDB on a local hard drive or a remote share? Do multiple users have it open or is just one copy running?

[AndyKennedy]

hi

Code is posted below.

Dilettante thanks for the input. Re VB Watch - I have put message boxes in the code to display elapsed time and have identified the lines which are taking the time - highlighted in the code below. NB - the code executes very rapidly for the first 33 array entries, only then does it slow right down (4 - 6 seconds to execute each db stored procedure).

Each module being used (Form and 2 class modules) define a private variable to reference the Data Environment ie

Private mdsrDE As New deStColumba

Not sure if this would cause any contention eg the multiple connections concern you raised? In my dev environment the mdb is local, just one copy is running.

'Form level code
'===============

If gcolTherapyGroups Is Nothing Then
    ' collection is not populated
    Set gcolTherapyGroups = New TherapyGroups
    gcolTherapyGroups.LoadGroups
End If

'Therapy Groups class module
'===========================

Private mcolGroups As New Collection

Public Function LoadGroups()
    ' queries the database for all groups & then
    ' populates this collection class
    
    Dim RS As Recordset
    Dim grpArray() As Long
    Dim i As Long
    
    ' query database via stored proc to populate recordset
    Set RS = mdsrDE.rscmdGetAllGroups
    RS.Open
    If RS.RecordCount = 0 Then
        Exit Function
    End If
    ReDim grpArray(RS.RecordCount - 1)
    i = 0
    Do While Not RS.EOF
        grpArray(i) = RS("Group ID")
        i = i + 1
        RS.MoveNext
    Loop

    Set RS = Nothing

    
    ' for each Group ID in the array, create a TG object, set
    ' the ID value and call the retrieve method to populate
    ' the object fully; then add to the collection
        
    Dim newGroup As New TherapyGroup
    
    For i = 0 To UBound(grpArray)
        newGroup.GroupID = grpArray(i)
        newGroup.RetrieveGroup
        mcolGroups.Add newGroup, "k" & Str(newGroup.GroupID)
        Set newGroup = Nothing
        Set newGroup = New TherapyGroup
    Next 'i
   
End Function

'Therapy Group class module
'==========================

Property Let GroupID(GID As Long)
    Dim RS As Recordset
    mdsrDE.cmdGetAllGroupWithID GID 
    Set RS = mdsrDE.rscmdGetAllGroupWithID
    If RS.RecordCount = 0 Then
        Err.Raise 535, , "Therapy Group ID provided does not exist in database"
    Else
        mintGroupID = GID
    End If
    RS.Close
    Set RS = Nothing
End Property

Public Sub RetrieveGroup()
    ' query db on ID of group
    Dim RS As Recordset
    Dim i, j As Long
    Dim Days(6) As Long


    On Error GoTo ErrorHandler
    mdsrDE.cmdGetAllGroupWithID mintGroupID
    Set RS = mdsrDE.rscmdGetAllGroupWithID
    
    ' populate group dets
    mstrGroupType = RS("Group Type")
    mcurNormalGroupCost = RS("Normal Group Cost")
    mintMaxClients = RS("Max No of Clients")
    mstrDayEvening = RS("Day Evening")
    mblnActive = RS("Active Flag")
    mstrLocAbbrev = RS("Location Abbreviation")
    mstrTherapist = RS("Therapist")
    mstrAddressLine1 = RS("Line 1")
    mstrAddressLine2 = RS("Line 2")
    mstrPostTown = RS("Post Town")
    mstrPostCode = RS("Post Code")
    mintAddressID = RS("Group Address ID")
    RS.Close
    Set RS = Nothing
    
    mdsrDE.cmdGetCIsForGroup mintGroupID 
    Set RS = mdsrDE.rscmdGetCIsForGroup
    mstrNormalDay = ""
    If RS.RecordCount <> 0 Then
        ' go thru rs finding days for each open-ended recurring appointment
        i = 0
        Do While Not RS.EOF
            If RS("IsRecurring") And RS("NoEndDate") Then
                mintCIInternalID = RS("CI Internal ID")
                Days(i) = Weekday(RS("Calendar Item Start"), vbUseSystemDayOfWeek)
                mstrNormalTime = Str(Hour(RS("Calendar Item Start"))) & ":" & Format(Minute(RS("Calendar Item Start")), "00")
                i = i + 1
            End If
            RS.MoveNext
        Loop
        For i = 1 To 7
            For j = 0 To UBound(Days)
                If Days(j) = i Then
                    If mstrNormalDay <> "" Then
                        mstrNormalDay = mstrNormalDay + ", "
                    End If
                    mstrNormalDay = mstrNormalDay & WeekdayName(i, True)
                    Exit For
                End If
            Next
        Next
        HasOutlookSessions = True
    Else
        HasOutlookSessions = False
    End If
    RS.Close
    Set RS = Nothing

End Sub

[dw85745]

Taking a quick look see, I would check your array dimension after this:

   ' query database via stored proc to populate recordset
    Set RS = mdsrDE.rscmdGetAllGroups
    RS.Open
    If RS.RecordCount = 0 Then
        Exit Function
    End If
    ReDim grpArray(RS.RecordCount - 1)

As I recall Access sometimes returns funny results with .RecordCount
unless you use ".MoveLast" prior to obtaining the count.
Using .MoveLast should give a RecordCount = to all the records in the table
otherwise funky results. Hence maybe the array is not getting dimensioned correctly.

[AndyKennedy]

hi dw - pretty sure you're right about record count being dodgy in Access without moving the pointer - but unfortunately doing a movelast prior to dimensioning the array does not alter the behaviour...

I shall try asking for the post to be moved to to the db automation section as suggested

rgrds Andy

[si_the_geek]

Thread moved to 'Database Development' forum as requested

One issue is that you do not close the recordset in LoadGroups, you should add these lines:

    If RS.RecordCount = 0 Then
        RS.Close
        Set RS = Nothing
        Exit Function
    End If
...
    Loop

    RS.Close
    Set RS = Nothing

You should also do the same in Let GroupID just before the Err.Raise


I suspect you don't need RS.Open in LoadGroups, so would recommend commenting it out. If you get an error the code on the line(s) after it then uncomment it, otherwise remove it.