I've been testing how to optimize the times on my macro execution where I use a lot of nested classes.
(comma = decimal separator)
- The procedure time for the whole job is: 13,6176926 seconds without dumping memory.
- The time for dumping the memory after the job + the job time is: 33,02271 without dumping memory.
-
- The time for the whole job while dumping the lower class is: 32,364528 seconds.
- The time for dumping the memory after the job + the job time is: 32,9384943 seconds.
-
- The time for the whole job while dumping every class while they stop being used is: 33,2625806 seconds.
- 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