0

I've been testing how to optimize the times on my macro execution where I use a lot of nested classes.

(comma = decimal separator)

  1. The procedure time for the whole job is: 13,6176926 seconds without dumping memory.
  2. The time for dumping the memory after the job + the job time is: 33,02271 without dumping memory.

-

  1. The time for the whole job while dumping the lower class is: 32,364528 seconds.
  2. The time for dumping the memory after the job + the job time is: 32,9384943 seconds.

-

  1. The time for the whole job while dumping every class while they stop being used is: 33,2625806 seconds.
  2. The time for dumping the memory after the job + the job time is: 33,2630745 seconds.

So I am wondering, is there any way to make this more efficient? Is taking more time dumping the memory than the actual job.

A bit of the code where I am dumping the memory while working is here:

Public Sub VuelcaPronosticos()

    Dim arr As Variant: arr = DimensionaArray()
    Dim Encabezados As New Dictionary: Set Encabezados = CargaEncabezadosPronos()

    Dim Modo As Variant
    Dim centro As Variant
    Dim fecha As Variant
    Dim tramo As Variant
    Dim pronostico As Variant
    Dim MiFecha As Fechas
    Dim x As Long: x = 1
    Dim FilaInicial As Long
    For Each Modo In m_Modo.Keys
        For Each centro In Modos(Modo).Keys
            For Each fecha In Modos(Modo).Centros(centro).Keys
                Set MiFecha = Modos(Modo).Centros(centro).Fechas(fecha)
                FilaInicial = x
                For Each tramo In MiFecha.Keys
                    x = FilaInicial
                    For Each pronostico In MiFecha.Tramos(tramo).PronosKeys
                        arr(x, 1) = centro
                        arr(x, 2) = CDate(fecha)
                        arr(x, 3) = Modo
                        arr(x, 4) = pronostico
                        arr(x, Encabezados(tramo)) = MiFecha.Tramos(tramo).Pronosticos(pronostico).Valor
                        x = x + 1
                        MiFecha.Tramos(tramo).BorrarPronostico pronostico
                    Next pronostico
                    MiFecha.BorrarTramo tramo
                Next tramo
                Modos(Modo).Centros(centro).BorrarFecha fecha
                FilaInicial = FilaInicial + x - 1
            Next fecha
            Modos(Modo).BorrarCentro centro
        Next centro
        m_Modo.Remove Modo
    Next Modo

    With Hoja59
        .Rows("2:" & .Rows.Count).Delete
        .Range("A2").Resize(UBound(arr), UBound(arr, 2)).Value = arr
    End With

End Sub

I think that with this code you can see the deep of the code and its classes. Any insight on how could i reduce the total execution time with this?

Edit: I'm using a Timer class that PeH shared once for the timming purposes:

Option Explicit
Private Type LARGE_INTEGER
    lowpart As Long
    highpart As Long
End Type

Private Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As Long

Private m_CounterStart As LARGE_INTEGER
Private m_CounterEnd As LARGE_INTEGER
Private m_crFrequency As Double

Private Const TWO_32 = 4294967296# ' = 256# * 256# * 256# * 256#

Private Function LI2Double(LI As LARGE_INTEGER) As Double
Dim Low As Double
    Low = LI.lowpart
    If Low < 0 Then
        Low = Low + TWO_32
    End If
    LI2Double = LI.highpart * TWO_32 + Low
End Function

Private Sub Class_Initialize()
Dim PerfFrequency As LARGE_INTEGER
    QueryPerformanceFrequency PerfFrequency
    m_crFrequency = LI2Double(PerfFrequency)
End Sub

Public Sub StartCounter()
    QueryPerformanceCounter m_CounterStart
End Sub

Property Get TimeElapsed() As Double
Dim crStart As Double
Dim crStop As Double
    QueryPerformanceCounter m_CounterEnd
    crStart = LI2Double(m_CounterStart)
    crStop = LI2Double(m_CounterEnd)
    TimeElapsed = 1000# * (crStop - crStart) / m_crFrequency
End Property
Damian
  • 5,152
  • 1
  • 10
  • 21
  • 1
    @urdearboy sorry didn't explain that for my Excel the comma is a decimal separator. – Damian Jun 09 '20 at 15:55
  • This is probably better suited for Code Review although it looks like you answered your own question: Don't dump the memory – urdearboy Jun 09 '20 at 15:58
  • @urdearboy in the end either I dump the memory, or Excel does. I cannot avoid that. The total execution time won't change... – Damian Jun 09 '20 at 15:58
  • If I understand correctly, code optimization is not on-topic for the SE, and you may want to re-frame the question slighlty, for example to ask how garbage collector does its job and if it can be improved upon. How did you time how long the garbage collector of VBA takes to release memory? – Victor K Jun 09 '20 at 16:00
  • Thank you for your suggestion @VictorK, edited my question and added the code with the timer I'm using to control the times. – Damian Jun 09 '20 at 16:04
  • Not sure if this is 100% relevant but [this](https://stackoverflow.com/questions/51065566/what-are-the-benefits-of-setting-objects-to-nothing) may be of interest. VBA is not garbage collected which is inconsistent with your title – urdearboy Jun 09 '20 at 16:13
  • @urdearboy thank you, already read this topic on the past, but it's talking about object in local procedures. My problem comes with class objects that I build and then destroy when no longer need them. If I don't do that, Excel does at the end consuming a large amount of time because there may be thousands of elements when I'm done. – Damian Jun 09 '20 at 16:19
  • Looking at comments [to this answer](https://stackoverflow.com/a/4106181/8597922) it appears that Excel will count references anyway, so you unlikely to gain anything from doing the clean-up yourself. It looks like you are using a lot of objects in a larger scope than the sub and I think it is more likely you can gain efficiency by looking at individual classes: how they are instantiated, stored, passed and terminated, or at your dicitionaries/collections. – Victor K Jun 09 '20 at 16:29
  • They are terminated by the Event `Class_Terminate()` if I don't do that manually. As for instantiated only when I need them.. And what do you mean by how are they stored, @VictorK? – Damian Jun 09 '20 at 16:42
  • 1
    @Damian For example `m_Modo` is not instantiated in the sub, so is it stored in the class scope, global scope, elsewhere? In the end I would say that as long as there are no circular references between objects or classes storing controls (buttons and the like) you would not gain much by improving memory deallocation. Instead it makes sense to focus on your algorithm and class architecture to make sure you do not use any more memory than you need. – Victor K Jun 09 '20 at 17:23
  • Thank you @VictorK, I don't think there are circular references. A class can be accessed by different classes, but not at the same time, so I won't be going forward and backwards through my objects. The thing is that the class might hold information for like 2,5k people for a whole month at intervals of 30 minutes for example. I know is massive data so I guess there is nothing to do here. – Damian Jun 09 '20 at 17:30

0 Answers0