Efficiency issues? Function takes 7-10 seconds to complete/refresh

**TheSmileyCoder** · Mar 26 '11, 04:36 PM

I haven't looked through your code as its quite lengthy. I will maybe give it a go later.
What I would suggest first is to turn of redrawing while your code runs. Add Docmd.Echo False at the start of your code and Docmd.Echo true at the end.
You should also add an error handler in which you turn the drawing back on, otherwise you will be stuck with an empty screen if an error occurs.

**Knut Ole** · Mar 27 '11, 09:02 AM

i appreciate that!

it's especially the two segments at line 47-62 and line 73-93, they eat up most of the time consumed. i might have made them rather chunky...

**ADezii** · Mar 27 '11, 10:11 PM

It appears to me that the long processing time has nothing to do with the efficiency of the Code, but rather with the nature of the Task. Among other things, you are dynamically Deleting/Creating Controls on none less than 67 Sub-Forms, a tall order in and of itself. There are a few items that I noticed which may provide small gains in processing time:

Always explicitly refer to the Object type Libraries to which Objects belong.
Code:
```
Dim RecSetSortAll As DAO.Recordset 
Dim RecSetRooms As DAO.Recordset
```

If you are only moving Forward within a Recordset, make it a Forward Only Type Recordset:

Code:

Set RecSetSortAll = CurrentDb.OpenRecordset("qSortAll", dbOpenSnapshot, dbOpenForwardOnly)
Set RecSetRooms = CurrentDb.OpenRecordset("qAllRoomsList", ", dbOpenSnapshot, dbOpenForwardOnly)

Not sure if it is worth it to create a Recordset for a Single Field Lookup, try a DLookup() instead:

Code:

dStart = DLookup("[RStartDate]", "DatePointer")
          'instead of
Dim RecSetDatePoint As Recordset
Set RecSetDatePoint = CurrentDb.OpenRecordset("DatePointer")
dStart = RecSetDatePoint.Fields("RStartDate").Value

I see no Clean Up chores being performed, namely:

Code:

RecSetSortAll.Cose 
RecSetRooms.Close
Set RecSetSortAll = Nothing 
Set RecSetRooms = Nothing

I didn’t have much time to look over the Code thoroughly, so if I am incorrect in any of my assumptions, please forgive me.

**Rabbit** · Mar 28 '11, 05:40 AM

Rather than a subform for each room, why not one form with all the rooms?

**Knut Ole** · Mar 28 '11, 07:59 AM

thanks all,

rabbit: would that save me a lot?

**TheSmileyCoder** · Mar 28 '11, 10:17 AM

I have some code that will at least help you to isolate which parts take the most time. The code should be placed in a class module.
At the bottom of teh module is an example of how to use it.

Code:

Option Compare Database
Option Explicit
Option Base 1

'***********  Class variables  ****************
Private lngStart As Long
Private lngLastEvent As Long
Private strEvent() As String
Private strLog As String
'Reference function
Private Declare Function GetTickCount Lib "kernel32" () As Long



Public Sub StartTimer()
    lngStart = GetTickCount
    lngLastEvent = lngStart
    ReDim strEvent(2, 1)
    strEvent(1, UBound(strEvent, 2)) = "Timer Started"
    strEvent(2, UBound(strEvent, 2)) = EndTimer
End Sub

Public Function addEvent(strInput As String, Optional bFromStart As Boolean = False)
    strLog = strLog & strInput
    
    
    ReDim Preserve strEvent(2, UBound(strEvent, 2) + 1)
    strEvent(1, UBound(strEvent, 2)) = strInput
    strEvent(2, UBound(strEvent, 2)) = EndTimer(bFromStart)
    lngLastEvent = GetTickCount
End Function

Public Function toString() As String
    Dim intI As Integer
    Dim lngSpacesEvent As Long
    Dim lngSpacesTime  As Long
    lngSpacesEvent = maxLen(strEvent(), 1)
    lngSpacesTime = maxLen(strEvent(), 2)
    
    For intI = 1 To UBound(strEvent, 2)
        toString = toString & addSpaces(strEvent(1, intI), lngSpacesEvent) & " : " & addSpaces(strEvent(2, intI), lngSpacesTime, False) & vbNewLine
    
    Next
End Function

Public Function EndTimer(Optional bFromStart As Boolean = False) As String
    Dim EndTime As Long
    If bFromStart Then
        EndTime = (GetTickCount - lngStart)
    Else
        EndTime = (GetTickCount - lngLastEvent)
    End If
    
    
    Dim s As Integer
    s = EndTime / 1000
    Dim ms As Integer
    ms = EndTime Mod 1000
    If s > 1 Then
        EndTimer = s & "s, " & ms & "ms"
    Else
        EndTimer = ms & "ms"
    End If

End Function


Private Function maxLen(myArray() As String, intCol As Integer)
    Dim lngMax As Long
    Dim intI As Integer
    For intI = 1 To UBound(myArray, 2)
        If Len(myArray(intCol, intI)) > lngMax Then
            lngMax = Len(myArray(intCol, intI))
        End If
    Next
    maxLen = lngMax
End Function
Private Function addSpaces(strInput As String, lngLength As Long, Optional bAfter As Boolean = True) As String
    'Add spaces
    If bAfter Then
        Do While Len(strInput) < lngLength
            strInput = strInput & " "
        Loop
    Else
        Do While Len(strInput) < lngLength
            strInput = " " & strInput
        Loop
    End If
    addSpaces = strInput
End Function



' ***********  Example usage  ****************


'Public Sub testSW()
'    Dim mySW As New TSCWatch
'
'    mySW.StartTimer
'
'    'Run code here
'
'    mySW.addEvent "Code 1 finished"
'
'
'    'Run more code here
'
'    mySW.addEvent "Code 2 finished"
'
'    'Run code here
'
'    mySW.addEvent "Total Time", True
'
'    Debug.Print mySW.toString
'
'    'Cleanup
'        Set mySW = Nothing
'End Sub

Example output:

Code:

Timer Started  :       0ms
First Code bit :     657ms
2nd code bit   :  2s, 31ms
Total Time     : 3s, 688ms

**Knut Ole** · Mar 28 '11, 12:52 PM

thanks a lot, smiley..
what takes time, apparently, is the clearing of 67 subforms (line 69), and drawing arrows (line 103).

i had the subforms opened/closed for each, while i have now made it so they open all on first use, then not open/closed again before all are closed at end. this saved me approx. 2-3 seconds in average.

im still at 5 seconds average (4.6 - 6.9, mostly at 4.6). however, there are few drawings on the calendar atm, compared to a full one - which could set me back many seconds. in other words, still very short of acceptable levels.

rewriting the whole thing to one subform is a formidable job. can anyone confirm this would give substantial gains? anyone with more concrete information as to exactly which lines are responsible for the time consumed?

the code is in a "module," by the way. any reason it should be in a "class module" or embedded in the calendar form instead?

thank you so much again!

**TheSmileyCoder** · Mar 28 '11, 01:09 PM

Hi Knut

I dont think you would gain anything noticeable by placing the code in the calender form. Class modules are something else entirely. (My listed watch for instance is a class).

Im sorry to tell you, but if you have used the listed approaches by ADezii and the DoCmd.Echo False I mentioned, then I dont think there is much more to be gained, since both deleting and creating controls takes some time.

Why have you choosen the approach of 67 subforms? What reasoning is behind the choice?

**Knut Ole** · Mar 28 '11, 01:34 PM

ok, thanks smiley.
the reason for subforms is first of all it seems orderly. it makes for no relative position information for the drawings, as the subform position on the mainform takes care of that.

another reason, discovered later, is that it's not easy to draw things perfectly in access - the pixels/cm/twips are not perfectly aligned, so i get some drawings being one pixel off. (seems insignificant, but creates some major off-positions in calendar).

so, im not looking forward to making all drawing positions completely relative to the whole thing - though if anyone has a clear-cut way of approaching this, i'd be very glad for some input. im thinking i'd have to create some off-set position constants to use with each drawing? im just afraid i'll never get it accurate due to the problems of twips/pixels mentioned above.

thank you so much so far,
more input on efficiency greatly appreciated (tho i might be reaching a roof here)

**Knut Ole** · Mar 29 '11, 01:08 PM

for the record:

using only one subForm in which all arrows are drawn greatly reduces the drawing time. apparently it's the opening and closing of forms that take time.

thanks all,

**TheSmileyCoder** · Mar 29 '11, 09:25 PM

Im glad that you got it sorted.

Out of curiosity, how much of an efficiency increase did it give? How long time does your form take to draw now?

**Knut Ole** · Mar 30 '11, 08:27 AM

i went immediately down to 1.1 s. with one subform.

having added some more draw objects (hor/vert lines etc.), im currently back up at around 2.3-3.4 secs. (it's funny, btw, how the drawing time varies by 50% with the exact same job...?)

thanks a lot for your help!

**TheSmileyCoder** · Mar 30 '11, 08:42 AM

Just to make sure, you did use the tip on turning off redrawing (DoCmd.Echo False), until the update is finished?

Especially when drawing you should see a good performance boost on that.

**Knut Ole** · Mar 30 '11, 08:55 AM

i did, but frankly, with or without echos i get exactly the same results... (intermittently 2.3s and 3.4s...)

perhaps it's because i disconnect the subform and the drawings are done in the background in the first place, and when reconnecting subform to mainform, all the new changes are there already... so the drawing process is not visible to the user in the first place.

last question, btw, you know the right code for refreshing/requiering and especially for the code for the "refresh all" button, so that all tables, queries are refreshed before my code runs? it's not really updated every time i make a change in my user-end inferface, and i have to sometimes manually "refresh all..."

thanks again!

Efficiency issues? Function takes 7-10 seconds to complete/refresh

Efficiency issues? Function takes 7-10 seconds to complete/refresh

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment