Potential memory leak when reading data for a database

36 views
Skip to first unread message

Brody Leather

unread,
Sep 8, 2021, 4:10:48 AMSep 8
to Excel-DNA
Hi Govert,

I have been struggling with Excel running out of memory, I have a function that gets given and date and then checks against a database to ensure that it is not a public holiday and then outputs a date that is one day forward if it its  a public holiday. 

My issue comes when this process is repeated many times fast the memory usage of excel continues to go up. I'm not sure if objects are not being released correctly or if there is another issue.

Here is the code associated with the issue. I am experiencing the same across all the functions where I read from a data base.

    Sub EnsureInitilizedHolidays()
        ' Opens and loads public holidays, Database must be kept alongside AddIn

        Dim dbPath As String
        Dim dbPathFinal As String

        dbPath = ExcelDnaUtil.XllPath
        dbPath = dbPath.Remove(dbPath.Length - 40, 40)

        dbPathFinal = dbPath & "HolidaysTo2070.accdb"

        If dbHolidays Is Nothing Then
            Dim dbPathTest As String = "C:\PortfolioManager\HolidaysTo2070.accdb"
            dbHolidays = New DBEngine().OpenDatabase(dbPathTest)
            rsTimeSeries = dbHolidays.OpenRecordset("Holidays")
            rsTimeSeries.Index = "DateHolidays"
        End If

    End Sub

    <ExcelFunction(Description:="Checks to see if a date is a good day and moves to the next good day if it falls on a holiday or weekend. If the month is crossed it will move to the previous good day")>
    Function GoodDateCheck(CurrentDate As Date)
        EnsureInitilizedHolidays()

        Dim HFlag As Integer = 1
        Dim WKendFlag As Integer = 1
        Dim WeekDayNo As Integer = 1
        Dim DateOut As Date = CurrentDate
        Dim MonthCrossed As Integer = 0

        Do While HFlag = 1 Or WKendFlag = 1
            'Tests for holiday
            With rsTimeSeries
                .Seek("=", DateOut)
                If Not .NoMatch AndAlso .Fields!Dates.Value = DateOut Then
                    HFlag = 1
                Else
                    HFlag = 0
                End If
            End With

            'Tests for weekday
            WeekDayNo = Weekday(DateOut, FirstDayOfWeek.Monday)

            If WeekDayNo = 6 Or WeekDayNo = 7 Then
                WKendFlag = 1
            Else
                WKendFlag = 0
            End If

            If MonthCrossed = 1 Then
                If HFlag = 1 Or WKendFlag = 1 Then
                    DateOut = DateOut.AddDays(-1)
                End If
            Else
                If HFlag = 1 Or WKendFlag = 1 Then
                    DateOut = DateOut.AddDays(1)
                End If
            End If

            Do While CurrentDate.Month <> DateOut.Month
                Do While HFlag = 1 Or WKendFlag = 1 Or MonthCrossed = 0
                    'Tests for holiday
                    With rsTimeSeries
                        .Seek("=", DateOut)
                        If Not .NoMatch AndAlso .Fields!Dates.Value = DateOut Then
                            HFlag = 1
                        Else
                            HFlag = 0
                        End If
                    End With

                    'Tests for weekday
                    WeekDayNo = Weekday(DateOut, FirstDayOfWeek.Monday)

                    If WeekDayNo = 6 Or WeekDayNo = 7 Then
                        WKendFlag = 1
                    Else
                        WKendFlag = 0
                    End If

                    If HFlag = 1 Or WKendFlag = 1 Or MonthCrossed = 0 Then
                        DateOut = DateOut.AddDays(-1)
                        MonthCrossed = 1
                        HFlag = 1
                        WKendFlag = 1
                    End If
                Loop
            Loop
        Loop

        dbHolidays = Nothing
        rsTimeSeries = Nothing

        Return DateOut


    End Function

Govert van Drimmelen

unread,
Sep 8, 2021, 4:19:23 AMSep 8
to Excel-DNA
--------------------------------------------------
Excel-DNA is now registered on GitHub Sponsors.
Easily add a monthly donation to your private or corporate GitHub account.
--------------------------------------------------

Hi Brody,

Most of the code looks good to me, except for the end of the function.

I suggest you just remove the two lines at the bottom of your function:
        dbHolidays = Nothing
        rsTimeSeries = Nothing

You don't want to recreate these objects every time the function runs - you want just one connection to the database and one set-up for the recordset index.
Then every time the function runs, it would use the existing database connection and recordset set-up.
This should have no memory problems, and be blindingly fast too.

-Govert

Brody Leather

unread,
Sep 9, 2021, 6:08:12 AMSep 9
to Excel-DNA
Hi Govert, 

Thank you for having a look, I have removed those lines but am still running into the same problem. I run many similar functions that read data from a data base do calculations and then return a value. 

Using a couple thousand of these functions and recalculating every second fills up the ram with more than 200mb every couple of minutes. 

All the functions work as I would expect them to however the ram never releases and eventually crashes excel. 

I am running excel 2019 and 365 and run into the same issue, I through perhaps it was that the objects were not getting released but I am beginning to think it is actually an excel issue perhaps?

Govert van Drimmelen

unread,
Sep 9, 2021, 6:18:27 AMSep 9
to exce...@googlegroups.com

Hi Brody,

 

If fixed, the database access pattern should not be a problem, and the way you had it before looks problematic to me.

Maybe there are some more instances of similar problems, or I’m not seeing all the code (for example – where is dbHolidays declared?).

 

The Access database engine would do some internal caching, though that should not grow without bounds. Especially if you’re not making multiple instances of the “DBEngine” and database all the time.

Also Excel caches strings internally, so that often looks like a memory leak but should not grow out of bounds.

 

You can isolate things a bit by changing the implementation of the database access function to just return some fixed or random value, and seeing how that affects your memory usage.

 

I’m guessing there is also another part that is managing or triggering the recalculation, and your problem might be there.

 

I’m also happy to arrange for some private support to dig in further.

If you’re interested you are welcome to contact me directly.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/6c78f87e-f655-4af4-963e-a46b529a24adn%40googlegroups.com.

Brody Leather

unread,
Sep 9, 2021, 6:54:40 AMSep 9
to Excel-DNA

Hi Govert, 

I have declared dbHolidays in the public module that the functions  are in.

Option Explicit On
Imports Microsoft.Office.Interop.Access.Dao
Imports ExcelDna.Integration
Imports Microsoft.Office.Interop.Excel

Public Module Holidays

    Dim dbHolidays As Database
    Dim rsTimeSeries As Recordset

    'Links to a Database and gets public holidays, Need to find way around full file path
    Sub EnsureInitilizedHolidays()
        ' Opens and loads public holidays, Database must be kept alongside AddIn

        Dim dbPath As String
        Dim dbPathFinal As String

        dbPath = ExcelDnaUtil.XllPath
        dbPath = dbPath.Remove(dbPath.Length - 40, 40)

        dbPathFinal = dbPath & "HolidaysTo2070.accdb"

        If dbHolidays Is Nothing Then
            Dim dbPathTest As String = "C:\PortfolioManager\HolidaysTo2070.accdb"
            dbHolidays = New DBEngine().OpenDatabase(dbPathTest)
            rsTimeSeries = dbHolidays.OpenRecordset("Holidays")
            rsTimeSeries.Index = "DateHolidays"
        End If

    End Sub

Perhaps a private session maybe a good idea. 

Thanks for the assistance.

VBA Tutorial

unread,
Sep 9, 2021, 6:42:22 PMSep 9
to exce...@googlegroups.com

Patrick O'Beirne

unread,
Sep 10, 2021, 5:26:04 AMSep 10
to Excel-DNA
briannonaluuluu that links brings me to "This video shows you how to record multiple mouse left clicks with VBA using Excel."
What has that got to do with the question?

I tried broleather's code in plain VBA, adjusting syntax as required, and no memory leak occurs. It's stable for 10,000 recalcs, memory 117MB

When you say "Using a couple thousand of these functions and recalculating every second fills up the ram with more than 200mb every couple of minutes. " 200MB is nothing, is the out of memory really happening there, or somewhere else? Or do you mean it increases by 200MB every 2 minutes and eventually crashes at, what, 2000 MB?

Or is it hitting an infinite loop somewhere and simply not exiting the Do loops, giving rise to a "Not responding"? Maybe put in a counter and display some status by OutputDebugString to see what is happening.

P

Brody Leather

unread,
Sep 10, 2021, 6:28:49 AMSep 10
to Excel-DNA
Hi Patrick, 

So when I run the code, I have a few thousand cells filled with similar formulas drawing information from the same database. On 32bit excel the ram fills up fast and crashes out around the 2GB mark. On a 64bit it fills up the ram but does not crash, from what I can tell it starts to unload into VRAM and release some of the other resources. 

It is filling up 200MB plus of ram every 10 or so minutes until it is fill. 

Thanks for the response. 
Reply all
Reply to author
Forward
0 new messages