Optimising Queries - Speed Tests

[isladogs]

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/speed-comparison-tests-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

 

[CJ_London]

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.

'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

 

[pbaldy]

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.

 

[Minty]

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 )

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

 

[isladogs]

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

 

[CJ_London]

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

 

[pbaldy]

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.

 

[CJ_London]

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.