All geek questions in one place

Are module properties of the VBA class slow compared to accessing custom type fields?

I am trying to develop a macro with a class module, but get / let seems to take a very long time compared to UDT. I'm really interested in why this is so, can anyone explain this? I just found discussions that talk about executing the / sub function, which seems to be just as fast.

The current problem is setting up a property that takes about 3000 ms for a class (for two million dates) and 120 ms for doing the same using UDT.

I am trying to decide whether I should advise macro developers to avoid using class modules when they need to get or set many properties. Using only this as data, I should, but maybe you have different views.

I would like to understand why this is so slow. Maybe I'm just doing something wrong.

Code example:

Public Type Participant
    Name As String
    Gender As Integer
End Type

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

Sub TimeUDT()
   Dim i As Long
   Dim startMs As Long
   startMs = GetTickCount
   Dim participants(1 To 1000000) As Participant
   For i = 1 To 1000000
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print GetTickCount - startMs
End Sub

Sub TimeCls()
   Dim i As Long
   Dim startMs As Long
   Dim participants(1 To 1000000) As New clsParticipant
   startMs = GetTickCount
   For i = 1 To 1000000
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print GetTickCount - startMs
End Sub

And the class module (named clsParticipant):

Private iGender As Integer
Private sName As String

Public Property Let Gender(value As Integer)
   iGender = value
End Property
Public Property Get Gender() As Integer
   Gender = iGender
End Property

Public Property Get Name() As String
   Name = sName
End Property
Public Property Let Name(value As String)
   sName = value
End Property
+4
source share
2 answers

Firstly, I highly recommend using a high-resolution timer, so you don't need to test so many iterations. See CTimer with QueryPerformanceCounter.

Here is your baseline on my machine, iteration 10K, timer with high accuracy

Sub TimeUDT()
   Dim i As Long
   Dim timer As New CTimer
   timer.StartCounter
   Dim participants(1 To 10000) As Participant
   For i = 1 To 10000
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print "Elapsed time: " & timer.TimeElapsed & " ms"
End Sub

Elapsed time: 1.14359022404999 ms

, . :

Sub TimeCls()
   Dim i As Long
   Dim timer As New CTimer
   Dim participants(1 To 10000) As New clsParticipant

   timer.StartCounter
   For i = 1 To 10000
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print "Elapsed time: " & timer.TimeElapsed & " ms"
End Sub

Elapsed time: 24.9600996727434 ms


Sub TimeCls()
   Dim i As Long
   Dim timer As New CTimer
   'Dim participants(1 To 10000) As New clsParticipant
   Dim participants(1 To 10000) As clsParticipant
   For i = 1 To 10000
       Set participants(i) = New clsParticipant
   Next i

   timer.StartCounter
   For i = 1 To 10000
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print "Elapsed time: " & timer.TimeElapsed & " ms"
End Sub

Elapsed time: 4.66722880515984 ms

4 ( , ). iGender sName , , Let .

Sub TimeCls()
   Dim i As Long
   Dim timer As New CTimer
   Dim participants(1 To 10000) As clsParticipant
   For i = 1 To 10000
       Set participants(i) = New clsParticipant
   Next i

   timer.StartCounter
   For i = 1 To 10000
      'participants(i).Name = "TestName"
      'participants(i).Gender = 1
      participants(i).sName = "TestName"
      participants(i).iGender = 1
   Next
   Debug.Print "Elapsed time: " & timer.TimeElapsed & " ms"
End Sub

Elapsed time: 1.71887815565976 ms
+7

Webb , , , .

, , , , .

3 , , , , .

, . , , 10 100 , . , , , . , , , , .

:

Sub Test()
  Dim T0 As Single

  T0 = timer
  TimeCls_a
  Debug.Print , timer - T0

  T0 = timer
  TimeCls_b
  Debug.Print , timer - T0

  T0 = timer
  TimeCls_c
  Debug.Print , timer - T0

  T0 = timer
  TimeCls_c_up
  Debug.Print , timer - T0

  T0 = timer
  TimeCls_c_dn
  Debug.Print , timer - T0

  T0 = timer
  TimeCls_c_all
  Debug.Print , timer - T0
End Sub

Sub TimeCls_a()
   Dim i As Long
   Dim T0 As Single
   Dim participants(1 To NCYCLES) As New clsParticipant

   T0 = timer
   For i = 1 To NCYCLES
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print "TimeCls_a:", timer - T0
End Sub

Sub TimeCls_b()
   Dim i As Long
   Dim T0 As Single
   Dim participants(1 To NCYCLES) As clsParticipant
   For i = 1 To NCYCLES
       Set participants(i) = New clsParticipant
   Next i

   T0 = timer
   For i = 1 To NCYCLES
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print "TimeCls_b:", timer - T0
End Sub

Sub TimeCls_c()
   Dim i As Long
   Dim T0 As Single
   Dim participants(1 To NCYCLES) As clsParticipant
   For i = 1 To NCYCLES
       Set participants(i) = New clsParticipant
   Next i

   T0 = timer
   For i = 1 To NCYCLES
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print "TimeCls_c:", timer - T0
End Sub

Sub TimeCls_c_up()
   Dim i As Long
   Dim T0 As Single
   Dim participants(1 To NCYCLES) As clsParticipant
   For i = 1 To NCYCLES
       Set participants(i) = New clsParticipant
   Next i

   T0 = timer
   For i = 1 To NCYCLES
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next

   For i = 1 To NCYCLES
       Set participants(i) = Nothing
   Next i
   Debug.Print "TimeCls_c_up:", timer - T0
End Sub

Sub TimeCls_c_dn()
   Dim i As Long
   Dim T0 As Single
   Dim participants(1 To NCYCLES) As clsParticipant
   For i = 1 To NCYCLES
       Set participants(i) = New clsParticipant
   Next i

   T0 = timer
   For i = 1 To NCYCLES
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next

   For i = NCYCLES To 1 Step -1
       Set participants(i) = Nothing
   Next i
   Debug.Print "TimeCls_c_dn:", timer - T0
End Sub

Sub TimeCls_c_al()
   Dim i As Long
   Dim T0 As Single
   Dim participants() As clsParticipant
   ReDim participants(1 To NCYCLES)
   For i = 1 To NCYCLES
       Set participants(i) = New clsParticipant
   Next i

   T0 = timer
   For i = 1 To NCYCLES
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next

   ReDim participants(1 To NCYCLES)
   Debug.Print "TimeCls_c_al:", timer - T0
End Sub
0

Source: https://habr.com/ru/post/1536617/

More articles:


All Articles