Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 11-03-2018, 09:21 AM   #1
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,558
Thanks: 55
Thanked 1,227 Times in 1,208 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Mythbusters - Make Table vs Append Query

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.
Attached Files
File Type: zip BloatTest.zip (1.52 MB, 58 views)

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline  
The Following 3 Users Say Thank You to theDBguy For This Useful Post:
isladogs (11-03-2018), NauticalGent (11-03-2018), Tera (03-23-2019)
Old 11-03-2018, 09:43 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,306
Thanks: 534
Thanked 937 Times in 888 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Mythbusters - Make Table vs Append Query

Quote:
Originally Posted by theDBguy View Post
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...
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline  
Old 11-03-2018, 10:25 AM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,873
Thanks: 114
Thanked 2,971 Times in 2,705 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Mythbusters - Make Table vs Append Query

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'

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 11-03-2018 at 11:46 AM.
isladogs is offline  
The Following User Says Thank You to isladogs For This Useful Post:
Tera (03-23-2019)
Old 11-03-2018, 10:31 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,558
Thanks: 55
Thanked 1,227 Times in 1,208 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Mythbusters - Make Table vs Append Query

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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline  
The Following User Says Thank You to theDBguy For This Useful Post:
Tera (03-23-2019)
Old 11-03-2018, 02:25 PM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,636
Thanks: 95
Thanked 1,498 Times in 1,413 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Mythbusters - Make Table vs Append Query

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.
Galaxiom is offline  
Old 03-23-2019, 12:54 PM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,873
Thanks: 114
Thanked 2,971 Times in 2,705 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Mythbusters - Make Table vs Append Query

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
Attached Files
File Type: zip BloatTest v2 - CR.zip (1.50 MB, 26 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline  
The Following 2 Users Say Thank You to isladogs For This Useful Post:
jdraw (03-23-2019), theDBguy (03-23-2019)
Old 03-23-2019, 02:55 PM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,326
Thanks: 40
Thanked 3,666 Times in 3,535 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Mythbusters - Make Table vs Append Query

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.


__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline  
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Make Table or Append Query? GBalcom Theory and practice of database design 1 02-12-2013 11:25 AM
VBA for make new table and append 2 querys dani9 Modules & VBA 3 05-24-2011 01:16 AM
Remove duplicate records without using append query and make a table Ghoztrider Queries 2 04-12-2011 11:34 AM
Make Table & Append Queries? prism solutions Queries 4 12-02-2009 07:41 AM




All times are GMT -8. The time now is 03:28 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World