Potential memory leak when reading data for a database

[Brody Leather]

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]

--------------------------------------------------

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]

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]

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]

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]

just check out youtube.com/c/vbatutorialcode brah

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/181a10e6-cfd2-4547-8ecb-78bedd8cc538n%40googlegroups.com.

[Patrick O'Beirne]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/CANW0d4yTKJh%2BQ_v_R65WU8WYU%3DVjKfJaMeZGepg6Z0pUAzw3Kw%40mail.gmail.com.

[Brody Leather]

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.