none
Undefined Bloat Source RRS feed

  • Question

  • Experts -

    I have a little application here that isn't very big (front end < 2MB, back end < 10 MB), that requires trimming any unnecessary data usage due to extreme slowness of anything MS Access when working over the VPN. 

    I have implemented some measures which have helped a lot, but the conundrum currently being faced is an unknown source of front-end bloat when importing new data.  I have some code that runs against a Sql Server datawarehouse, imports the data and chops it up into a bunch of little tables, all of which reside in the back end.  I had to tweak the SQL being passed to Sql Server, after which I noticed during the import process the front end will grow to about 3MB for no reason as it only contains 2 tiny tables.  After compacting it goes back down to around 1.7 MB where it should be. 

    Anyway, the problem is that every KB of data matters when dealing with this VPN and an extra MB or even 1/2 MB makes a 10 minute difference with this application. 

    I already checked and there are no tables being created and then dropped during the import process. 

    So - any idea where this bloat is coming from?  Could it be the very long string I am building in VBA to pass to Sql Server?  Could opening recordsets do this? (they do get closed after being used)

    Thanks in advance! 


    Bonediggler

    Monday, May 22, 2017 7:39 PM

Answers

  • Hi Bonediggler,

    I try to search and I find that the cause is if you create a query in your code then it will increase the size rapidly and memory not get free until you repair and compact your database.

    so it is better if you do not create query in your code and try to use already saved query.

    also Access generate many Temp files which not free the memory until you repair and compact your database.

    because of this reasons the size of the front end increase.

    if you cannot use stored queries then you can try to call the compact and repair after completing your database operations from code. so that it free the memory every time.

    Sub Test()

    Dim repaired As Boolean

    ' You can simply make up a name for the destination file.
    repaired = RepairDatabase("C:\Users\Public\Documents\Database.accdb", _
                                  "C:\Users\Public\Documents\DatabaseRepaired.accdb")

    MsgBox repaired

    End Sub

    ' Input values: The fully-qualified path names of the source and destination
    ' database files.
    Function RepairDatabase(sourceDb As String, destDb As String) As Boolean

    ' Compact and repair the database. You can use the return value of the
    ' CompactRepair method to determine if the file was successfully compacted.
    RepairDatabase = Access.Application.CompactRepair(sourceDb, destDb, True)

    End Function

    Compact and Repair a Database

    further if you are using DAO in your code then you can refer link below to prevent bloat.

    How to prevent database bloat after you use Data Access Objects (DAO)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Bonediggler Tuesday, May 23, 2017 1:25 PM
    Tuesday, May 23, 2017 2:28 AM
    Moderator

All replies

  • Hi,

    So, you have a front end linked to a back end and you're importing data into the back end but bloat is showing on the front end, correct? Is there any bloat showing in the back end as well?

    Monday, May 22, 2017 7:42 PM
  • Correct.

    I wouldn't call the back-end size increase "bloat" as tables are being truncated and data imported, so its expected.

    I did just observe the file size of both back and front ends during import, and it looks like as the back end expands, the front end does as well - not exactly in lockstep but not too far off either.


    Bonediggler

    Monday, May 22, 2017 7:51 PM
  • Okay, it's all speculation, but I would suspect Access uses the front end as a workspace since I am assuming the import transaction is processed by the front end, correct? If so, you might consider using a "temp" db to process the import instead. For example, from the front end, when the user wants to start the import process, rather than import the data, create a new database file and export to it the code to do the import process and then execute it. Also, you have two options once the import is completed: either delete the temp db or delete it later before starting the next import.

    Just a thought...

    Monday, May 22, 2017 8:01 PM
  • Wouldn't that just shift the bloat from the front end to the temp db? 

    Bonediggler

    Monday, May 22, 2017 8:06 PM
  • Wouldn't that just shift the bloat from the front end to the temp db? 

    Bonediggler

    It would but the intent was to not affect the VPN speed since after the import process, the user will continue to work in the front end, which (I'm hoping) did not bloat.

    Hope it makes sense...

    Monday, May 22, 2017 8:23 PM
  • Sorry - I should have been more clear.  Its actually the time required for import which is the problem, which I don't think would be mitigated with this approach (?)

    After import everything runs fine.


    Bonediggler

    Monday, May 22, 2017 8:26 PM
  • Sorry - I should have been more clear.  Its actually the time required for import which is the problem, which I don't think would be mitigated with this approach (?)

    After import everything runs fine.


    Bonediggler

    Oh, okay. I thought you said earlier, everything is fine only after you do a C&R.

    So, if you're saying the app is slow during the import process, then I am not sure what else you can do about it other than maybe doing the import in chunks rather than one big data transfer all at once.

    Just a thought...

    • Edited by .theDBguy Monday, May 22, 2017 8:34 PM
    Monday, May 22, 2017 8:31 PM
  • It not clear why the front end increase in size would have any effect or concern in regards to performance?

    That front end is placed on each computer and NEVER makes the trip over the network. So if the front end grows by 5x, it not going to increase network traffic.

    It is a possible that you NOT placing the front end on each machine? Front end growth should not affect network traffic since that front end is local and placed on each PC.

    If you importing data to the front end and chopping it out as you point out, then again once the data is pulled into the front end then again growth of the front end should not cause any network traffic.

    If you are having to important data, process it local then a really great approach is on application start up is to create a local “temp” accDB file that you use for these temp tables and processing. So you simply on start-up re-create this temp file each time and you eliminated any and all bloat from “importing” data for temp processing.

    An example of creating that temp file can be found here:

    http://www.granite.ab.ca/access/temptables.htm

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Monday, May 22, 2017 11:05 PM
  • I wonder if record locking might be a contributing factor here? 

    What Record Locks are you using for your forms?

    What is your Default record locking set to (Options -> Client Settings -> Advanced)?


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, May 23, 2017 12:23 AM
  • Hi Bonediggler,

    I try to search and I find that the cause is if you create a query in your code then it will increase the size rapidly and memory not get free until you repair and compact your database.

    so it is better if you do not create query in your code and try to use already saved query.

    also Access generate many Temp files which not free the memory until you repair and compact your database.

    because of this reasons the size of the front end increase.

    if you cannot use stored queries then you can try to call the compact and repair after completing your database operations from code. so that it free the memory every time.

    Sub Test()

    Dim repaired As Boolean

    ' You can simply make up a name for the destination file.
    repaired = RepairDatabase("C:\Users\Public\Documents\Database.accdb", _
                                  "C:\Users\Public\Documents\DatabaseRepaired.accdb")

    MsgBox repaired

    End Sub

    ' Input values: The fully-qualified path names of the source and destination
    ' database files.
    Function RepairDatabase(sourceDb As String, destDb As String) As Boolean

    ' Compact and repair the database. You can use the return value of the
    ' CompactRepair method to determine if the file was successfully compacted.
    RepairDatabase = Access.Application.CompactRepair(sourceDb, destDb, True)

    End Function

    Compact and Repair a Database

    further if you are using DAO in your code then you can refer link below to prevent bloat.

    How to prevent database bloat after you use Data Access Objects (DAO)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Bonediggler Tuesday, May 23, 2017 1:25 PM
    Tuesday, May 23, 2017 2:28 AM
    Moderator
  • How to prevent database bloat after you use Data Access Objects (DAO)

    Hi Deepak,

    I am not convinced on the above.

    The above reference is very old and goes back to A2000 and A97. I have done hundreds of experiments to see what the effect is of NOT explicitely closing a recordset. When the recordset is declared within the Sub where it is used, I have NOT found any problems.

    I have no experience with the creation of queries (QueryDef) in code - I never use QueryDefs - instead all sql-strings are generated dynamically. This has never given any bloating to my applications.

    Imb.

    Tuesday, May 23, 2017 7:21 AM
  • Hi Imb-hb,

    I only share the information here that was described in the Microsoft Documentation.

    it just shows some possibility for this issue. it is also possible that user is not using DAO in his code.

    you had mentioned that the document is old.

    but you can see that it is using Microsoft DAO 3.6 Object Library.

    this same library is available in latest version of access. below snapshot is from Access 2016.

    so the same document can work for all access versions.

    I am agree with you that you had done hundreds of experiments. so if you think that this Microsoft Documentation need some changes then please try to provide your valuable feedback to Access User Voice.

    Welcome to Access Suggestion Box!

    we would like to hear from you and it will help us to create healthy forum environment.

    Thanks to share your experience with us, in future it will also help to other community members.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 23, 2017 7:51 AM
    Moderator
  • Deepak - I think you may be on to it.  The only significant change I made (before getting this problem) is throwing the whole SQL statement into a string variable - which was required as part of the code could change from day to day. 

    Unfortunately 1) I can't change this and 2) the slowness is only during import, not after.  It is the bloating during import which slows the process down.  The app works fine afterwards.

    I will keep looking for workarounds...


    Bonediggler

    Tuesday, May 23, 2017 1:25 PM