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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-28-2019, 04:44 AM   #1
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,352
Thanks: 100
Thanked 2,152 Times in 1,996 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Speed Comparison tests - Check Record Exists

This speed test was inspired by an example by UA member cheekybuddha in this post.

I've taken the same idea a bit further with another in my ever growing series of speed comparison tests on my website.
See item 7 - Check Record Exists

As in the UA example, the tests compare the time required to check the existence of a specified record in a large data table using 4 different methods: DLookup / DCount / SQLCount(*) / SQL Select

There are two similar versions of this set of speed tests.
The main difference is the search field is NOT INDEXED in one and INDEXED in the other.

Of course, in a real world application, fields being searched regularly should normally be INDEXED.
The index increases file size but dramatically reduce search times.
In this example, the search time was over 250x faster using indexing but there are some other interesting differences in the results for each version.

In each case, a 'reference' table tblSource containing 10,000 different UK postcodes (deliberately kept small to reduce file size) is used to populate the test table tblData which is initially empty.

In order to get a large data table, those records are appended repeatedly
For example, 100 batches (default) of 10,000 records to give a total 1 million records in tblData.

One RANDOM record is then replaced by a 'dummy' postcode 'XM4 5HQ' used in the record check.
For info, this postcode is used to sort letters addressed to Santa Claus!!!

Each speed test is was done repeatedly by looping through the code multiple times and measuring the total time
The number of loops used can be varied with default=100.
So, in this case 1 million records are looped through 100 times i.e. 100 million records checked.

In the non-indexed tests, DLookup & SQL SELECT were similar to each other and faster than DCount or SQL Count(*) - often MUCH faster
The difference depended on the position of the record being searched (as you would expect)

For the indexed field, all 4 results were similar - SQL SELECT was always slowest.
DLookup & DCount were, perhaps surprisingly, almost fastest (though not by much)
The real power of indexing was confirmed by increasing the test table to 10 million records.
The times required were identical to those for 1 million records.

In fact, I also tested with 30 million records - once again indexing meant the total times were the same.
However it took several hours to create that ridiculously large test table and the database grew to 1.9 GB - so NOT recommended!

See attached for the 2 example databases and a PDF version of the website article

I would appreciate feedback on any of the website articles - some produce some surprising results e.g. HAVING vs WHERE
If you wish to respond, the easiest way is probably to email me using the link in my signature line

__________________
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.


Colin
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; 01-28-2019 at 05:10 AM.
isladogs is offline   Reply With Quote
Old 01-28-2019, 05:49 AM   #2
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,131
Thanks: 79
Thanked 439 Times in 399 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Speed Comparison tests - Check Record Exists

Just out of curiosity, is there a significant speed difference between running a saved query and running a SQL string via VBA?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 01-28-2019, 07:12 AM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,352
Thanks: 100
Thanked 2,152 Times in 1,996 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Speed Comparison tests - Check Record Exists

I'm glad you asked me that ....
See http://www.mendipdatasystems.co.uk/s...s-6/4594478795

__________________
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.


Colin
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   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Tera (02-04-2019)
Reply

Tags
check record exists , indexing , speed tests

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mythbusters - Speed Comparison Tests - Having vs Where isladogs Code Repository 16 11-04-2018 07:27 AM
Another Mythbuster - Speed Tests - Conditional Updates isladogs Code Repository 0 11-02-2018 03:18 PM
Check to see if a record exists first... fat controller Modules & VBA 9 09-15-2014 08:33 PM
Check for exists record BlindPro Forms 2 06-23-2012 03:29 AM
Check to see if a record exists then do something Mr_Si Modules & VBA 5 03-15-2012 09:24 AM




All times are GMT -8. The time now is 09:58 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