Comparison of Grouped Aggregate Queries

First Published 3 Jan 2023                                                    Last Updated 6 Jan 2023                                                           Difficulty level :   Moderate

Section Links:
        The Task
        Tests / Results
        Query Execution Plans
        Query Execution Plans - Summary
        Additional Tests / Results
        Yet More Tests / Results
        Further Reading

1.   Introduction                                                                                                                                         Return To Top

      This is the fifteenth in a series of articles discussing various tests done to compare the efficiency of different approaches to coding.

      In this article, the efficiency of 5 different types of aggregate query are compared including the use of non equi-joins and subqueries.
      Unexpectedly, it then led into a long investigation into the effects of indexing in aggregate queries.

      The article was inspired by a suggestion from fellow Access developer Chris Arnold at the end of my presentation on Optimising Queries to the online
      Access Europe User Group in September 2022

      You can watch the YouTube video of that presentation at

2.   The Task                                                                                                                                               Return To Top

      Using DEMO data from my main schools database:
      Count the number of reading age (RA) tests (where >1) for each student that were a lower RA value than the most recent test.

      This is a simplified version of a real situation from my own school where the Special Educational Needs (SEN) department was required to provide a range of
      reports indicating the amount of intervention for students done over a period of time.

      The original version of this query gave the data obtained for one such test.
      Four other versions of the same query have also been tested here for comparison

      The 2 tables used here are cut down versions of the original SQL Server tables with dummy data

      PupilData (PD)
      1482 records; 9 fields; Indexes - PupilID (PK text), Surname+Forename (composite text), Reading (text)

      SENTestHistory (TH)
      4946 records; 5 fields; Indexes – SENTestHistoryID (PK autonumber), PupilID (FK text), Type (text), Level (text)

      Query Output:
      97 records, 7 fields (for all 5 queries)

      This is part of the aggregated query output from one of the queries. All 5 queries have been designed to provide the same output


      Reading age is expressed in years:months e.g. 13:07. Students take targeted tests aimed at a certain range e.g. 9:03 to 11:09

      Where a result is outside the limits for that test, values may be expressed as e.g. 9:03- or 11:09+
      In such cases, the student would be re-tested using a different test range

      Similar tests are done to measure the Spelling age and Arithmetic age and are also used to focus targeted support on those students with greatest need.

      Each test result is stored in the Level field of the SENTestHistory table.
      For historical reasons, the most recent test result is also recorded in the Reading field of the PupilData table.

      The five queries used in these tests were as follows:

a)   qryCountCriteria

      This is a standard grouped aggregate query on the two tables using an INNER JOIN with 2 WHERE filters and a HAVING filter on the aggregate field


b)   qryCountNonEquiJoin

      In this query, the unequal WHERE filter is replaced by a non-equi join. This query must be saved in SQL view


c)   qryCountCartesian

      In this query, the tables are not joined. All filtering is done using the WHERE and HAVING clauses


d)   qryCountStacked

      Here an aggregate query qryTH is used to get the aggregate values then this is joined to the PupilData table in qryCountStacked


e)   qryCountSubquery

      A subquery is used to get the aggregate values so no table joinis needed in the main query. The HAVING clause references the subquery.


3.   Tests / Results                                                                                                                                   Return To Top

      Each test consisted of the queries being run 20 times in succession and the total time measurered.
      Each test was repeated 10 times in a random order and the average times calculated.

      The first 4 tests were very similar with the criteria query the fastest and the cartesian query the slowest. This was exactly as I had expected.
      However, the differences between these 4 queries was minimal.

      I had expected the subquery version to be slower but not that it would take about 10 times longer! This was VERY surprising.

      I removed the index from the Level field used in aggregation.

      The first 4 queries were slightly faster with the overall order remaining the same.
      However, there was a significant improvement for the subquery version which, although still the slowest, was now only about 14% slower

      I then also removed the index from the Reading field in the PupilData table with which the Level is compared in the filter.

      Once again, the overall order remained unchanged. However, doing this took about 0.3 to 0.35 s off each time

      Overall, the standard query based on a table join and WHERE/HAVING criteria is the most efficient in these tests.
      I was surprised how well the cartesian query performed

      However, there are two obvious questions arising from these tests:

      a)   Why did REMOVING the indexes speed up all the queries?

      b)   Why does indexing have such a large adverse effect on the subquery performance?

      To investigate each of these, I carried out a large number of additional tests

4.   Query Execution Plans                                                                                                                         Return To Top

      I compared the query execution plans (QEP) using the hidden, undocumented JET ShowPlan feature. See my article: Show Plan . . . Go Faster

      a)   First of all, as a benchmark, I ran a grouped aggregate query on the SENTestHistory table alone with the Level field indexed then not indexed.
            The query SQL for each was:

SELECT TH.PupilID, Count(TH.Level) AS CountOfLevel
WHERE TH.Type="Reading"
HAVING Count(TH.Level)>1;

            The results showed that there was almost no difference between the times for each version.

            The query execution plans were as follows:

            Level Field Indexed

            Not Indexed


            Both QEPs were identical. The index on the Type field was used in each case and Rushmore query optimising technology was involved.

            I repeated the tests on a much larger table of student attendance marks with about 680K records.
            Once again, indexing had minimal effect on the times to run a grouped aggregate query on a single table

            This suggests the explanation for the differences shown in the original set of results must lay elsewhere.

            Next I compared the query execution plans for the 5 types of grouped aggregate query tested above

      b)   qryCountCriteria

            Both Fields Indexed

            Not Indexed


            Both QEPs were again identical. The available indexes were used in each case and Rushmore query optimising technology was involved.

      c)   qryCountNonEquiJoin

            Both Fields Indexed

            Not Indexed


            Both QEPs were again identical. Same comments as above

      d)   qryCountCartesian

            Both Fields Indexed

            Not Indexed


            Yet again, both QEPs were identical. Rushmore used on the SENTestHistory table with a temporary index on the PupilData table

      e)   qryCountStacked

            Both Fields Indexed

            Not Indexed


            Yet again, both QEPs were identical. Rushmore used on the SENTestHistory table with two sets of temporary index on the PupilData table

      f)   qryCountSubquery

            Both Fields Indexed

            Not Indexed


            Yet again, both QEPs were identical.
            Unfortunately JET ShowPlan does NOT give the execution plans for subqueries
            However, there is no mention of Rushmore being used elsewhere in the final query.

            That alone almost certainly makes the subquery less efficient i.e. slower.

5.   Query Execution Plans - Summary                                                                                                      Return To Top

      The first 4 queries all used the available indexes effectively with Rushmore query optimisation technology utilised for sorting and filtering.

      However, each of the QEPs were identical when the queries were run with the two indexes used in aggregation removed.
      So that explains why the order is unchanged.
      However, it does not explain why each query ran slower when indexed nor why the subquery was so adversely affected by the indexes

      The Microsoft help article: Create and use an index to improve performance states that you should consider indexing a field if all of the following apply:

      a)   The field's data type is Short Text, Long Text, Number, Date/Time, AutoNumber, Currency, Yes/No or Hyperlink.
      b)   You anticipate searching for values stored in the field.
      c)   You anticipate sorting values in the field.
      d)   You anticipate storing many different values in the field. If many of the values in the field are the same, the index might not significantly speed up queries.

      As a check, I compared the table index statistics:

      PupilData:   Records = 1482; Unique Index Values (Reading Field) = 99 (approx 6.6% of number of records)

      SENTestHistory:   Records = 4946; Index Values (Reading Field) = 138 (approx 2.7% of number of records)

      In my opinion, that should have made both fields well suited to indexing in this example

6.   Additional Tests / Results                                                                                                                   Return To Top

      As a further test, I altered the reading age data in both tables to only show the years as an integer value
      e.g. 7:11 => 7; 14:05 => 14 giving a range from 5 to 18
      NOTE: This was done just for the purpose of testing here. It would be of very little use for educational purposes

      Doing this reduced the number of unique indexed values to just 14 for the aggregated Level field and 13 for the Reading field in the PupilData table

      There were now 138 records returned by each query but the pattern was much the same as before

      Both fields indexed


      Both fields NOT indexed


      So it appears that the size of the original indexes wasn't the issue!
      Although I had no reason to suspect that using Count was an issue, I decided to eliminate it by using a different aggregate function Max rather than Count

      Both fields indexed


      Both fields NOT indexed

      Different values but same trend again . . .

      I also tested with the same data in linked SQL tables from the original dataset
      The first 4 queries were much the same as before but the subquery was many times slower still!

      Whilst running the subquery, the task manager showed high CPU, memory and power usage

      I now went back to the original dataset and started altering the criteria used in each query
      First of all, I removed the restriction that there had to be more than one result in the Count(Level) aggregate field.

      As a result there were now more records - 195 in total

      Both fields indexed


      Both fields NOT indexed


      Finally, with the exception of the subquery, the indexed versions all took about the same time as their non-indexed equivalent.
      The subquery took about 16s. That was about half the original time for the indexed field

      Next, I removed the restriction that the values counted in the Level field had to be less than the latest value in the Reading field.

      As a result there were now more records - 329 in total

      I discarded the non equi-join query as this was no longer relevant

      Both fields indexed


      Both fields NOT indexed


      BINGO! The indexed and non-indexed versions of each query were now very similar in terms of the time taken
      The subquery was no longer the slowest, only being beaten by the stacked query

      I decided to do one further test removing both sets of restrictions.
      The number of records was now 1423 and included almost all the students in the dataset.

      Both fields indexed


      Both fields NOT indexed


      Again, both sets of tests took almost exactly the same time. Indexing was no longer harmful, but it still offered no benefit.
      However, to my surprise, the subquery was now the fastest method of all taking about 5% LESS time than the other 3 queries.

      To me, this was remarkable!
      It is the very first time in 20+ years of using Access that I have EVER seen a subquery outperform a well optimised single query

      The charts below compare the relative speeds of all the queries in the original non-indexed test to the final non-indexed test.
      As you can see, the order is almost exactly reversed in the final simplified tests.
      Of course, the vertical axis scale 'over dramatises' the time differences.

        Original Query

  Final Query


      Unfortunately, the query designs were by now so simple that the query output was totally useless for the original purpose!

      Finally, I looked at the QEPs again for the final set of queries used above

      By now, it probably won't surprise you that in each case the QEPs were again identical for the indexed and non-indexed versions!
      Not only that, but they were effectively identical to those for the original set of queries.

      The only difference was that the order of events did change slightly for the stacked query, but the actual steps were exactly the same.

7.   Yet More Tests / Results - UPDATE 5 Jan 2023                                                                                   Return To Top

      After publishing this article, I decided to try one more set of tests.

      On a hunch, I decided to go back to the original query, retain both indexes on the Level and Reading fields, but force Access to ignore them.

      To remind you, these were the original results with both fields indexed

      And these were the original much faster results with both indexes removed

      In order to ignore the indexes, I just added an zero length string to the start of both fields used in the inequality filter.

      In other words, I changed the inequality used in each query from: TH.Level < PD.Reading to ("" & TH.Level) < ("" & PD.Reading)

      For example, qryCountCriteria:


      After making this change, these were the new average results:

      SUCCESS!!!!! All 5 queries ran faster than before with the standard criteria query being fastest and the subquery remaining slowest.


      We now have the best of both worlds! The queries were for the original task with both indexes included but ignored for the inequality filter.

      Also, as you probably expect by now, the query execution plans were almost identical to before, apart from the use of the changed inequality.

      The one exception was that the Cartesian query made better use of indexing

8.   Conclusions                                                                                                                                         Return To Top

      The use of indexes on suitable fields is generally beneficial in speeding up searches and sorts.

      However, it appears to offer no benefit on the fields used in aggregate queries.

      In many cases, indexing the fields used in aggregation (or compared with them) can make grouped aggregate queries run noticeably slower.

      Despite this, retaining the indexes but ignoring them in the inequality filter proved the most efficient method of all.

      In general, the standard grouped aggregate query involving joins and where criteria performs as well as any of the other more unusual variants.

      Using a subquery in aggregation is generally a bad idea unless the query has no conditions/restrictions associated with the aggregated field

      NOTE: In the near future, I plan to do two follow up articles provisionally called The Art of Indexing and The Joy of Subqueries   !!!!

      EDIT: 6 Jan 2023
      Perhaps some additional clarification is needed following a lengthy critique of this article by @ebs17 at Access World Forums

      This article was intended purely as a comparison of 5 different types of grouped aggregate query.
      At no time have I implied that any of the queries are fully optimised.

      There are several further improvements that can be made including the use of First vs Group By that was discussed in my earlier article Optimising Queries
      There is also an issue with the inequality filter though this doesn't affect the comparative speed tests.

      However, this article is already far longer than originally intended due to the lengthy digression on the effects of indexing.
      I am intending to return to all these points as part of my forthcoming article devoted to indexing.

9.   Download                                                                                                                                             Return To Top

      Click to download the example databases with all code used in these tests. The query execution plans are also included in each zip file.

      Comparing Aggregate Queries - INDEXED      Approx 2.1 MB (zipped)

      Comparing Aggregate Queries - NOT INDEXED      Approx 2.1 MB (zipped)

      Comparing Aggregate Queries - INDEXES IGNORED      Approx 2.1 MB (zipped)

10.   Further Reading                                                                                                                                   Return To Top

      I have not been able to find anything online explaining the effect of indexing on aggregate queries or why it disproportionately affects subqueries in Access

      Here is a generic Microsoft help article about indexing

      Create and use an index to improve performance

      Here is an interesting article about indexing in SQL Server

      Poor database indexing – a SQL query performance killer – recommendations

11.   Feedback                                                                                                                                             Return To Top

     Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

     Please also consider making a donation towards the costs of maintaining this website. Thank you

      Colin Riddington                     Mendip Data Systems                           Last Updated 6 Jan 2023

Return to Speed Test List Page 15 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Return to Top