Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Micron
    I don't think the article by the DB Guy is stating there are only two methods of creating a temp table

    However the table is created, when it is populated, it is self evident that the file size will increase.
    Some people call that bloat, other say its not because that's what should happen

    If you keep adding & deleting records or making tables repeatedly, the overall file size will of course increase each time ... unless of course you do a compact & repair.

    I accept there are issues with the sample file in that link
    You are perfectly correct that you should use LOF to measure the size of an open file.
    In fact I contacted DBG a couple of months ago to point out that exact point & attached a new version of his utility (Bloat Test v2 - CR)
    Attached is my updated version of his utility using LOF instead of GetFileSize

    You will notice how much more responsive the app is
    The reason is not that it makes the actual tests faster.
    In fact DBG was building in a delay needed to make it possible for his tests to work at all
    Using LOF solves that issue

    However the comparative results are unchanged:
    Make Table is always faster, dramatically so if the file size is larger.
    File size increase is similar for each method

    Independently of the article I quoted, I've done my own tests on synchronising data by a number of different methods including both of these.
    My timer tests are more precise (to centisecond accuracy) & I use LOF to measure file sizes


    Here is a summary of my average test results :

    Click image for larger version. 

Name:	SyncData19.PNG 
Views:	20 
Size:	25.0 KB 
ID:	37884

    NOTE: Tests 1-4 are based on one set of tables. Tests 5-9 on another.
    Note the results for tests 3 & 4 and again for 8 & 9
    In these synchronisation tests, those 2 methods are fairly similar in terms of both time & 'bloat' though make table wins out in both respects
    The synchronization test app is also attached (SyncDataExample v5.3)

    I've also discussed each of the methods used in an extended article on my website: http://www.mendipdatasystems.co.uk/s...a-1/4594514001

    Hope this helps clarify a few things
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  2. #17
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Fairly certain I have seen db bloat due to design changes that had nothing to do with populating data. At least during early stages of development. Maybe repeatedly creating and deleting tables will not have noticeable impact. However, don't think that approach is possible in an accde executable. So that may be a consideration.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #18
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't think the article by the DB Guy is stating there are only two methods of creating a temp table
    I began with "Is there another way to interpret the following quoted text from the site?".
    However, the need to use temporary tables cannot be avoided sometimes. In those cases, there are two main approaches to creating and populating a temporary table.
    OK, I guess he's right because creating a temp table def would not, I suppose, be a "main" approach.

    A temp table def would likely not be practical, but the point I wanted to make is that if it was practical in a particular case, it causes no bloat whatsoever. Once the procedure finishes, the table is gone and never existed anywhere but machine memory.

    The same sort of bloat is generally accepted to be expected when continually creating and deleting temp queries. Here's where the temp query def certainly has a practical use. In fact, I'd use that over continually creating and deleting a table (or stored query) any day, because it too resides only in machine memory. Note that none of what I'm saying here has anything to do with db file size growth as a result of adding records. AFAIC, that's not bloat and I think it would be better if we didn't refer to normal file size growth as bloat. In fact, it would serve us all better if we had a more suitable term for "temporary table", because there's nothing temporary about the table per se.
    Last edited by Micron; 03-18-2019 at 09:30 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #19
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry, I'm not clear what your main point is here.

    1. Using table defs is a very useful tool but perhaps a completely different discussion.

    Creating a table/field using a table def will of course have negligible effect on file size. Nor will making a table using a data definition query
    Similarly saved queries take up almost no space. Similarly saving a form/report with no code


    At the risk of stating the blindingly obvious it is populating tables and adding code that increases file size

    2. I was originally responding to June's point back in post #10 - using DELETE/APPEND rather than MAKE TABLE/APPEND

    To quote that paragraph of DBG's article in its entirety:
    If you are highly concerned about database bloat (the database file size steadily increases with each use) caused by using temporary tables, then the best approach is to avoid using temporary tables altogether. However, the need to use temporary tables cannot be avoided sometimes. In those cases, there are two main approaches to creating and populating a temporary table.
    As I said I used to believe that MAKE TABLE was a bad idea due to 'bloat' but several years ago realised that wasn't actually the case.
    DBG's article was the first time I had seen anyone else share my views on the matter

    3. I agree that the word 'bloat' is misleading - see my comment in post #16
    Ditto the use of the phrase 'temporary tables' to denote tables that are permanent but used as a temporary holding area for data.
    I often use the phrase 'buffer tables'. Others call them 'staging tables'

    4. June wrote:
    Maybe repeatedly creating and deleting tables will not have noticeable impact. However, don't think that approach is possible in an accde executable.
    Yes its perfectly possible - have a button to run the make table query / SQL and then (optionally) open the table created.
    No different to the other approach in that respect

    5. However I do have one note of caution - repeatedly making / destroying tables in the same 'session' does eventually lead to instability.

    For example, I have a commercial app which processes external JSON data, analyses it, creates new tables for the data & populates the tables.
    During development & extreme testing, I did that repeatedly with different JSON files (Over 100 new tables in a few hours)
    Unfortunately some online JSON files include errors which the app also handles
    Eventually, I got the dreaded message that 'the database was in an unrecognised format' or similar
    Thankfully DECOMPILING fixed that issue

    For that reason, the app includes this info in the Help file:
    ISSUE: I get an error such as ‘the database has crashed’ after creating several new tables, closing then reopening the database
    WORK-ROUND: Repeatedly creating & deleting tables can cause database instability (especially if the JSON files contain errors)
    Decompiling the database followed by recompiling and compacting should fix this issue
    Also, regular backups should be made
    In other apps, where I need to repeatedly populate the same data fields into a 'temporary table' I will indeed reuse the same table, emptying it & repopulating each time.
    But I do so for stability ...NOT because its faster or creates less bloat

    For example the backend datafile of my main schools app is updated every night using 30 linked CSV files.
    These are imported into a similar number of 'buffer tables', processed as necessary then used to synchronise the main tables (in SQL Server) using update/append/delete sql statements/queries
    When the process is finished, the buffer tables are emptied and the app is compacted.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #20
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    1. Using table defs is a very useful tool but perhaps a completely different discussion.
    If you can't see how a temporary created via a temporary tabledef isn't part of the discussion, then I'm just not getting through or I don't understand them and how this would contribute to not creating bloat. This

    Set tdf = CurrentDb.CreateTableDef("")

    creates a tabledef in memory. When it expires, there's no trace of it in the db - never was part of the stored tables, never existed in the nav pane. Doesn't contribute to bloat. That's my message.

  6. #21
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I believe DBG also has a sample DB on using a temp Database.
    I have an app where I create a temp database on open and then create and delete temp tables as needed in the temp database.
    There is only very slight bloating in the front end related to establishing a link to those tables. IIRC, the bloat was only a kb or so after a good period of use.

  7. #22
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Micron

    I'm really not sure what point you are trying to make that hasn't already been covered

    I've already explained why I didn't refer to table defs in my original answer.

    I've already agreed that creating a table definition will not add anything to file size.
    But as I've already said, creating an empty saved table using a make table query or data definition query will have minimal effect on file size

    I've also agreed that using the phrases 'bloat' and 'temporary tables' are often inaccurate and unhelpful
    The example in my last post was referring to what is sometimes referred to as a side end database where all the processing is done to manage updates in the backend.

    So what is it that I'm not comprehending in your view?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. List Box to Text Box
    By manics31 in forum Forms
    Replies: 3
    Last Post: 11-01-2018, 11:11 AM
  2. Replies: 2
    Last Post: 03-07-2018, 11:00 PM
  3. Replies: 8
    Last Post: 12-13-2017, 10:38 AM
  4. Use List box to populate text box with existing text
    By Stephenson in forum Programming
    Replies: 5
    Last Post: 10-07-2016, 10:07 PM
  5. list and text box??
    By Zugalug in forum Forms
    Replies: 2
    Last Post: 10-04-2011, 10:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums