Mythbusters - Make Table vs Append Query

[theDBguy]

In the spirit of the other mythbuster demos available here, such as comparing the speed between a WHERE or HAVING query, I would like to submit the attached demo.


There has been long argument regarding which approach creates more bloat for a database, using a Make-Table query or using an APPEND query. The attached demo uses both approaches and compares both the speed and effect in the database's file size after each operation.


You can replace the data set with your own to get a more appropriate result that is more applicable to your own situation. Please share your results, so all of us can make an educated conclusion in the end.


You can read more details about my own experiment in this article.


I would also appreciate any comments regarding this demo.


Thank you.

 

[Uncle Gizmo]

You can replace the data set with your own to get a more appropriate result that is more applicable to your own situation.

If anyone is looking for a nice big dataset to test against, then I happened upon this website:-

Statistical Review of World Energy – consolidated dataset

when I was looking for some information on global warming.
BP provide quite a big set of data, I think there's a quarter of a million rows, dome in Excel format and some in (CSV) Comma Separated Value Format...

 

[isladogs]

Many thanks for posting this example.

Like many others, I had read the warnings about not using Make Table queries because they cause database bloat.
Of course they do .... as additional records are being added to the database.

Only very rarely does anyone comment on the fact that using append queries has exactly the same effect on database size

I came across this example at the DB Guy's website some time ago and ran several tests on different datasets of my own as well as that provided in the example.

In each case I found the following:
a) database bloat was similar using either method
b) using make table queries is usually far faster than using append queries

However one caveat:
I believe that repeatedly creating and therefore destroying new tables can lead to database instability

My JSON Analyser application is designed to create new tables based on imported JSON data.
For development purposes, I have in the past created up to 100 new tables in a day whilst testing this app very thoroughly.
Very occasionally, that leads to the database becoming unstable.
Decompiling always solves the issue (so its code being corrupted rather than data issues)

However, its NEVER been an issue for end users. Although clients are warned about this issue together with decompiling as the solution, none have ever reported it happening in 'normal use'

 

[theDBguy]

Good point, Colin. It reminded me that in the link to the original article I posted above, it also refers to another article on how to create a Temporary Database as a means to avoid bloat.


Cheers!

 

[Galaxiom]

Anything that creates records then deletes them is going to bloat. I have long been an advocate of using a temporary database for which I coined the term "Side End". A good location is in one of the user's AppData folders. I run the local copy of the Front End from there too.

The main advantage of appending records to an existing table is that the data types of the fields are predefined so Access doesn't make dopey guesses based on the first few rows.

 

[isladogs]

Attached is an updated version of the DBGuy's bloat test utility which I have already forwarded to him

The original version used GetFileSize to measure the size of the current file.
To get this to work, it is necessary to build in a delay and as a result the tests appeared to take far longer than the measured time.

The new version uses the LOF function instead of GetFileSize which means the app responds far faster than before
This of course does not affect the actual time taken by the 2 tests or the files sizes measured

I have independently compared the speed of make table & append queries with several different tests and in each case got the same outcomes:
a) file size increase is almost identical for each
b) time taken is very similar
However in both respects, make table performs slightly better than append.
For example, see tests 3/4 & 8/9 in the synchronise data tests

 

[CJ_London]

I would expect make table to be faster than append if the existing table being appended to has indexes. If there are no indexes then I would expect the same performance.

I do agree and use the 'side end' for temporary tables to avoid bloat - my basic philosophy is to never need to delete data in the BE as BAU. Flag as 'to be deleted' yes, then in a maintenance process, delete these records, then compact.