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.
Make-Table Query
The simplest approach is to use a Make-Table query to dynamically create the temporary table. Of course, you will have to delete the temporary table after you're done with it because Access will throw an error when it tries to create a table that already exists.Delete and Append Query
The other approach is to create your temporary table's structure beforehand and just populate it at runtime. With this approach, you will first run a Delete query to empty out any remaining data in the temporary table before you execute an Append query to populate the table with the necessary data.So which one causes more bloat?
I have always thought that creating and recreating database objects over and over would cause database bloat more than just deleting and adding new data. However, some experts swear that it's actually the opposite. So, to settle the score, I have decided to create a little experiment. To see the result of my little experiment, skip to the end. To try it out for yourself, click on the image below to download the demo file I used for this experiment. By the way, the demo version was created using Access 2007.
Is there a better approach?
Actually, there is... If you regularly use temporary tables in your database projects, you can avoid the issue of database bloat by using a temporary database instead of temporary tables. I will elaborate more on this approach in my next blog; but essentially, you can create a temporary database with all the temporary tables in it and just link to them from within your current database. Once you are done processing the data, you can simply delete the temporary database file since you will create a new one next time you need it.My test results
Based on my little experiment, I was able to confirm that both methods (make-table and append query) result in database bloat. And what's interesting to me was that both methods produced about the same amount of bloat, as you will see in the results table if you run the experiment yourself. What I did find significant in my experiment was that using a make-table query ran so much faster than using an append query. So, based on this experience, I will probably change my tune and recommend using the make-table approach over the append query.Additional info
Please note that I used the term "database bloat" very loosely in this article. Some experts do not consider creating records on purpose as database bloat because we all know that adding records is supposed to increase the file size of the Access database.If you have any comments or questions, please let me know.
Original post date: June 24, 2013
No comments:
Post a Comment