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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-04-2019, 10:03 AM   #1
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,293
Thanks: 105
Thanked 2,492 Times in 2,288 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Optimising Queries - Speed Tests

Allen Browne has an excellent web page devoted to various methods of improving query performance: http://allenbrowne.com/QueryPerfIssue.html
I thought it would be helpful to others to illustrate his suggestions by performing a series of speed tests showing the effect of each suggested change.

To reduce file size, the queries are based on cut down versions of 3 tables from the DEMO version of my School Data Analyser application. All data is for fictitious students in a fictitious school.

The aim of the query is to get the count of each type of pastoral incident recorded for each student in the year 2018. The query is also filtered to those students whose date of birth was in the year 2005.

There are 11 versions of the query with varying amounts of optimisation starting with a (deliberately) badly designed query and ending with the most optimised.
All queries return the same records (total = 882) but the times should get progressively faster each time (except for the final stacked queries test).

Each test is run several times to reduce natural variations caused by other processes that may be running in the background. The total time recorded is for the set number of loops. By default, the number of loops = 3

The fields used in each table to filter and sort the data are indexed to speed up searches:
The indexed fields are Surname, Forename, DateOfBirth, DateOfIncident

The average times recorded after running each set of tests 10 times was as follows:



As you can see, the times taken improved significantly from over 23 s originally down to about 0.46 s almost 50 times faster

If anyone can see ways in which the query can be further optimised, please do let me know!

I have attached the example database used in these tests together with the JET ShowPlan (query execution plans) for each query.
A PDF summarising the effect of each change is also attached

Alternatively, you can view an extended version of the PDF on my website: http://www.mendipdatasystems.co.uk/s...s-8/4594556613

EDIT:
There is an updated version of the OptimiseQueries.zip file in post #5.
This fixes an issue with the code to collect workstation info as well as the form resizing issue described by CJ_London in post #2
Attached Images
File Type: png OptQ-AvgResults.PNG (25.3 KB, 212 views)
Attached Files
File Type: zip ShowPlan.zip (10.4 KB, 23 views)
File Type: zip OptimiseQueries.zip (729.2 KB, 28 views)
File Type: zip Optimise Queries PDF.zip (930.5 KB, 56 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.


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; 03-05-2019 at 10:41 AM.
isladogs is offline   Reply With Quote
Old 03-04-2019, 11:20 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,773
Thanks: 40
Thanked 3,494 Times in 3,381 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Optimising Queries - Speed Tests

I come out with broadly the same result, but H & I are faster than J and stacked (K) is fastest (but not in your screenshot). This is mine. Note font size had a bug and failed on this line, presumably on the 5th iteration.
Code:
 
'CR v5290 - next line restored in place of individual control properties below
'.FontSize = .FontSize * sngFactor '(my comment out)


EDIT
I have multiple screens, 3 are 1680 x 1050 21", one of which is portrait, one is 1920 x 1080 23". There is a 5th which is a 21" touch screen, which although connected is not active at the moment - I use that when testing touch applications and have to make it my primary monitor to activate the touch events. The issue was the same on all monitors I tested
Attached Images
File Type: jpg Capture.JPG (56.1 KB, 191 views)
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button

Last edited by isladogs; 03-05-2019 at 10:56 AM. Reason: Merged comment from deleted post with agreement of CL_London
CJ_London is offline   Reply With Quote
Old 03-04-2019, 04:30 PM   #3
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,816
Thanks: 12
Thanked 4,020 Times in 3,957 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Optimising Queries - Speed Tests

Quote:
Originally Posted by CJ_London View Post
I have multiple screens, 3 are 1680 x 1050 21", one of which is portrait, one is 1920 x 1080 23". There is a 5th which is a 21" touch screen,
5 monitors?!? You have seriously triggered my jealousy reflex.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 03-05-2019, 02:30 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,284
Thanks: 151
Thanked 1,683 Times in 1,655 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Optimising Queries - Speed Tests

5 Monitors is just silly
(Goes off to grumble about desk being too small...)
Anyway here is the comparison from the spaceship laptop, very similar to CJ's times - no font issues here and I have 3 screens , (Sounds so inadequate )
PHP Code:
TestTypeID    LoopCount    RecordCount    TestTime
A            3        882            13.53
B            3        882            1.01
C            3        882            0.86
D            3        882            0.68
E            3        882            0.69
F            3        882            0.70
G            3        882            0.63
H            3        882            0.67
I            3        882            0.59
J            3        882            0.61
K            3        882            0.53 
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

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.
Minty is offline   Reply With Quote
Old 03-05-2019, 10:38 AM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,293
Thanks: 105
Thanked 2,492 Times in 2,288 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Optimising Queries - Speed Tests

I think CJL must have spent so long looking at screens as a child that someone made him 'monitor monitor'

Anyway after an email discussion with CJL, I've now fixed the error he experienced with the form resizing code.
Whilst I can't replicate the error, I can see the reasons for it occurring.
Very odd that nobody had reported it as an issue until now

Attached is an updated version. This fixes both the form resizing code together and a separate issue with the workstation info button code

I've also run the tests another 20 times as below and repeated on 2 other PCs.
Further testing seems to indicate that test K is usually slightly faster than test J but the difference is typically very small in my testing



We all know Minty has a 'go faster' laptop and by comparison my first very slow test takes twice as long.
Of course, comparing different workstations shouldn't be the main issue here ....
However do note that my fastest test results are actually faster. .... SMUG GRIN.



A few extra points however.
1. There will always be some natural variation in results due to background processes running, CPU load etc
Hence, the tests should be run several times and averages taken.
After each set of tests, click the Save Results button. Click View Results to see the results history & the average values

2. One use for this utility is to provide a simple method of comparing different queries.
You can easily adapt it to replace the tables & queries with your own

NOTE: With CJL's agreement I've deleted several posts in this topic which focused on the resizing issue
Attached Images
File Type: png OptQ-AvgResultsNEW.PNG (25.6 KB, 118 views)
File Type: png OptQ-MainForm+Results.PNG (49.4 KB, 119 views)
Attached Files
File Type: zip OptimiseQueries v11.2.zip (716.7 KB, 18 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.


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; 03-05-2019 at 12:27 PM.
isladogs is offline   Reply With Quote
Old 03-05-2019, 04:16 PM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,773
Thanks: 40
Thanked 3,494 Times in 3,381 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Optimising Queries - Speed Tests

in case anyone is interested
1 for email
2 for research
3 for app I'm working on
4 for related forms such as vbe
5 for touch screen environment when required

fits nicely round a corner desk
had 4 screen setup for nearly 10 years (touchscreen somewhat less)

but thinking about chopping them in for fewer, bigger ones once I've finished redesigning the house
__________________
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   Reply With Quote
Old 03-05-2019, 05:05 PM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,816
Thanks: 12
Thanked 4,020 Times in 3,957 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Optimising Queries - Speed Tests

Is number 4 the portrait one? I have dual monitors with one portrait, and I like the VBE on it as I can see more code.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 03-05-2019, 11:57 PM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,773
Thanks: 40
Thanked 3,494 Times in 3,381 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Optimising Queries - Speed Tests

no - number 1 - outlook with message list at the top and selected message below. I have a lot of rules for when emails are received for putting to subfolders so they are on the left so I can see all of them. But I will occasional move the vbe over if required, or stretch across two screens so I can see two or more modules side by side.

__________________
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   Reply With Quote
Reply

Tags
optimise queries , speed tests

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Speed Comparison tests - Check Record Exists isladogs Code Repository 2 01-28-2019 07:12 AM
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
Optimising Sum Queries simon43 Queries 1 08-10-2004 06:52 PM
Optimising memory use and disk access to improve speed DanR General 7 05-28-2003 09:02 PM




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