Results 1 to 15 of 15
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Memory Leak using DAO (2002-SP3 & 2003-SP1)

    We've run into something that's a bit worrisome, and wondered what experience others have had. We have an Access .MDB as the front-end to a SQL Server database using ODBC linked tables. The front-end has a set of code which open a recordset of the main records using DAO, and then we analyze a second DAO recordset of detail transactions by looping though them, then update the main record that they belong to, and step to the next main record. It appears that even though we have statements to close the detail recordset and then set it to nothing, the memory used by the detail recordset is not being released, or at least is not being released entirely. The problem is that by the time we process 10,000 main records or so, the MSAccess.exe footprint has grown to between 150MBytes and 200MBytes. Unfortunately, we have 160,000 of these main records to process, along with about 1.6M detail records. To add to the issue, this process needs to be run on a monthly basis.

    We are working on converting the code to ADO to see if that solves the problem, but I can't find any description of a similar problem where DAO was identified as the problem. Has anyone else seen this kind of behavior?
    Wendell

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    It's been years since I tried doing stuff like that with that large a number of records (@ 160,000), so I don't recall the details. I know that DAO is exceedingly leaky in my opinion, but that may not be exactly what's happening. Are you hitting out of memory errors, or are you just concerned because of the huge growth of the database? If your code is using queries for anything (even SQL select statements in code), you're going to see growth in the mdb because Access creates temporary queries to process the SQL each time you run the code and doesn't discard that temporary object until you compact the database. It releases the memory used to create it and use it, but it has no way to shrink back down to its pre-query size. If that is your situation, your best bet might be to create a separate mdb used for nothing except running this code, open it remotely, and run the code from there. Of course, the utlimate workaround would be to do the processing in SQL Server and call it from a passthrough query using DAO or directly using ADO.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    Well, after several hours of coding, it appears using ADO does stop the memory leak. We still have some of it set to use DAO, so we are seeming some creep, but much less than before. Unfortunately, it looks like it has slowed down when we switched to ADO. At this point it's starting to look like a 24 hour process. More work ahead.

    I should also clarify what's going on - the database isn't bloating, the MSACCESS.EXE is growing in memory. This is being run remotely using Windows Terminal Services, and when the RAM usage get's to about 160MB, we begin getting system resource messages. If you ignore them, it eventually slows the server to a crawl, and the only recourse is to reboot the server. <img src=/S/puke.gif border=0 alt=puke width=60 height=15>
    Wendell

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    I haven't worked with terminal services, so I can't help there, and I've never monitored the exe in memory. It probably is slower if you apply it against the linked tables using the same SQL as you used for DAO. It should be faster if you go directly against the backend using the SQL OLEDb provider and set the command location to server side, but it depends on what you're doing.
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    What we actually resorted to was connecting via SQL Server views using ADO, and then running stored procedures to do the actual updates. However we are doing them one at a time, and I suspect if we could update many in a batch, things would go faster. I must say I've never tried to process this many records in this fashion either - it's a bit of a challenge.
    Wendell

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    Have you thought to put an "updated" field in the main table. And also to run the update database by a Windows scheduler.
    Run the process for 10,000 records from another database, the mark these as "updated".
    Quit Access.
    When the scheduler runs again the data base processes another 10,000 records.
    And on and on it goes until they are all processed.

    In this way you can still run the DAO process which is the fastest as you say.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    That's actually sort of the process we've adopted while we are trying to solve the problem. In doing more testing and debugging yesterday, it appears the real speed penalty is when you update a recordset one record at a time. I began a sweep about 24 hours ago, and it became apparent it was going to take about 48 hours to complete. After thinking about the problem at length, I decided there was a subset where I could easily determine the value to be set, so I ran a query that created new records in a local table, upsized that table to SQL, and then ran a SQL sproc against the data. In that manner I was able to update about 180,000 of the records in about 2 hours - including my development time. Then the process was run against the remaining records, and that completed early this morning. We'll have to streamline and automate the process, but the moral of the story is that scaling something up is size can have surprising performance impacts.
    Wendell

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    What were you trying to do with each main record that forced you to process one main record at a time, then to process the subset of records for each main record, could this have been done with one update query?

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    I would suspect that Wendell has a class evaluating each record and setting values based on the result. I've done this kind of processing in classes before, and it isn't anything that can be done with a simple query. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    Charlotte has pretty much described the problem - though we haven't made it into a class module or anything that sophisticated. There are actually two different processes - the simpler one calculates the time between a person first joining an organization, and the current date, or the date of death if applicable. However it is possible that there may be lapses in the span of time for a variety of reasons, so that in some cases several transactions must be evaluated to determine the correct time to be credited. The second is far more complex and involves a larger set of transactions.

    My original concern was that the analysis part of the process was going to be very slow, but it turns out that the actual updating of records is what takes most of the time. At this point it appears the fastest approach may be to create a temporary table of the calculated values and then use that in an update process in SQL Server. It's been an interesting problem thus far.
    Wendell

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    If you're using ADO, what about using a batch update?
    Charlotte

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    I've toyed with that idea some - right now we are using an sproc to do the update, and it's no faster than doing just a simple update on each record. I've never actually done a batch update using ADO - what are the issues and considerations? I'm pretty sure I don't want to try to update all 345K records in one shot - the possibility of a failure during the transaction and having to start over would be a worry. I think doing them in batches of 100 or even 1000 records might be feasible. Any guidance you can give would be appreciated.
    Wendell

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    Is there a possibility of doing these updates when the transactions are entered, then there is no need for a massive update all in one go.

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    On an ongoing basis that will happen for the status values, but the time span changes based on the day it is calculated for anyone who is not deceased. The other challenge is that this the starting point - we are extracting this information from an existing system and starting with 345K main records and 1.9M detail records. In the conversion process we have to calculate these values initially as the legacy system did not even attempt to do that sort of thing. It's a challenge.
    Wendell

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Memory Leak using DAO (2002-SP3 & 2003-SP1)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> It's been at least 5 years since I did this, so I'll have to rummage around in my archives and see if I can find exactly what I did to refresh my memory.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •