Left Join vs Inner Join

[ebs17]

why do you think it might be slower?

I have no real idea. JOIN and WHERE are comparative operations and are equivalent in the order in which the query is processed. This gives the SQL optimizer a choice of where to start. This is influenced by the subqueries I have shown, because now the queries in the FROM must first be evaluated in order. The show plan is visibly the same here, but maybe a lot more will happen internally.

The variants and optimizations relate to a specific task (existing tables, data contained, request for output). Even small changes to one of these points can necessitate very large changes, so the best solution found can only be determined by specific circumstances.
If, for example, the year of birth was not filtered, you could filter directly in PRecords for the year and immediately group using the two foreign keys and count the data records. This would have reduced the number of records from 17680 to 1717, and only these would need to be joined to the two primary tables.

The principle remains, therefore, to reduce data volumes quickly and at an early stage, if possible with lean operations. Best possible index utilization should be self-evident.

I see other construction sites that may be more productive. The index planning in your tables is not yet optimal. Always check the index window!

PupilData: Why is there an index on Forename? I would find a composite index on Surname and Forename useful, e.g. for sorting.

PRCodes: Index on Code is duplicate. The second index is of no use, but it also has to be managed and (theoretically) slows down write operations.

PRecords: Index on Code and PupilID are superfluous because they are duplicated. When you create a 1:n relationship with referential integrity set, the foreign key is automatically indexed because that's so important. However, this index is not superficially visible. However, you can see it via code-controlled reading in the table definition. Again, a composite index on PupilID and Code would make sense, in that order, because PupilID has the greater variability in values.
This index could be used in the grouping in the query using the two foreign key fields from PRecords:

GROUP BY R.PupilID, R.Code

The order of the JOIN groups in the FROM part could also play a role. A colleague said: We know that the jet optimizer is not the smartest. There might be some help there. This immediately gives rise to the idea that the show plan of the same query with the same tables could look different in a different database management system.

 

[isladogs]

There are some interesting points in your lengthy reply.

The tables are cut down versions taken from the demo version of one of my commercial apps for schools which has over 300 tables and well over 1000 queries (saved or SQL statements in VBA).
The indexes therefore reflect wider usage rather than for this example app alone.
However, you are correct that there are some duplicate indexes that I forgot to remove

In general, subqueries are slower and should be avoided if another better method exists.
It may be that any additional optimisation caused by your 'sequencing' is negated by the presence of subqueries.
I wonder also whether aliasing has any impact. If so, likely to be small.
You used Count(*) whereas I used Count(FieldName) which is probably slower.

I did try to test the effect of aliasing but to do so would have meant changing your query, so I left it for now.

The actual query is close to being fully optimised, so it probably isn't worth spending any more time on that.

However, a forthcoming speed test will compare subqueries, non-equi joins and more besides...possibly aliasing as well
If interested, you can find the complete list of published speed tests at

Speed Comparison Tests

This is a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your own workstations

www.isladogs.co.uk

BTW Although the demo has an Access BE, the actual app for schools uses a SQL Server BE and has been optimised for that.

 

[ebs17]

subqueries are slower

Can you say that in general?

I think you have to differentiate whether the subquery is in the FROM, SELECT or WHERE part. In the SELECT and WHERE part you often have correlated subqueries. These are not only executed once, but in the extremum per data record of the main query, which multiplies an effort. In this case, you don't need to be surprised about longer runtimes.

I see a subquery in the FROM part as uncritical, it is only executed once. You can also cascade multiple times. It would be important that there are no superfluous operations such as sorting.

 

[Pat Hartman]

I'm sorry you viewed my response as hostile. it appeared that you didn't understand that join types are not arbitrary. It sounded like you were changing the type because i told you the inner join was faster and that is NOT the reason to change the join type. Just because the two joins happened to return the same results with the current set of data does not mean they are interchangeable. The color was for emphasis since it is a critical point.

 

[Pat Hartman]

Jet does not optimize subqueries well so I recommend separate querydefs and joins unless a join won't solve the problem. Some people just write subqueries instead of joins for some reason that escapes me. If you need a subquery, you need a subquery so don't worry about it. Just like the join type. If you need a left join, then you need a left join so that is the query you build

 

[isladogs]

One very important point I completely forgot to mention when I posted the query execution plans in post #16.
JET ShowPlan doesn't handle subqueries at all. So it only gave a partial picture of what was happening with the subquery version.

I agree with Pat's comments in the last post. Subqueries are inefficient in terms of query execution.
See the comments on performance and conclusions by Allen Browne in his article http://allenbrowne.com/subquery-02.html.
However, subqueries can also be very useful
I use them when there isn't another good method of achieving the same results.

 

[ebs17]

These statements about sub-queries are still too general and not very differentiating. For a subquery like the one used above, I don't really need the assistance of an optimizer:

...
(
                  SELECT
                     PupilID,
                     Code,
                     MeritPts,
                     DeMeritPts
                  FROM
                     PRecords
                  WHERE
                     DateOfIncident BETWEEN #1/1/2018# AND #12/30/2018#
               ) AS R
...

Such very bad designs as in qryTestA , B, C etc. in the speed test, you can also say mistakes, but with a little manual skill they will not occur.

 

[isladogs]

These statements about sub-queries are still too general and not very differentiating. For a subquery like the one used above, I don't really need the assistance of an optimizer:

...
(
                  SELECT
                     PupilID,
                     Code,
                     MeritPts,
                     DeMeritPts
                  FROM
                     PRecords
                  WHERE
                     DateOfIncident BETWEEN #1/1/2018# AND #12/30/2018#
               ) AS R
...

Such very bad designs as in qryTestA , B, C etc. in the speed test, you can also say mistakes, but with a little manual skill they will not occur.

I think you may have missed the point.
There were no mistakes in the query designs. The first few queries were DELIBERATELY badly designed.
Did you actually read the article and/or watch the video?

The whole point of the article / video was to show the impact of various changes on optimising queries
To do that I needed something to compare against

 

[ebs17]

The first few queries were DELIBERATELY badly designed.

Of course, I got that. That wasn't a criticism of you or your performance.
Pat said: "reflect the real world and we are talking about real world scenarios"

In practice, such designs are actually not that rare, I see them often in forums. No SQL optimizer helps there either, so you were happy if it somehow worked.

 

[NauticalGent]

so you were happy if it somehow worked.

Exactly, and that is the beauty(?) of Access. It allows an ordinary Joe to perform like a programmer. It isn't until you join a forum like this one after some time has gone by that you begin to learn the "right" way of doing things.

 

[NauticalGent]

I would find a composite index on Surname and Forename useful, e.g. for sorting.

Did not know there was a such animal. I consulted Dr. Google and now I know - amazing what you can pick up by being an innocent bystander!

 

[Pat Hartman]

There's lots of things I learned about indexes when working with RDBMS's on the mainframe that I don't know will help with Jet/ACE. For example, when you have search criteria that has a lot of duplicates such as gender and marital status in a table of hundreds of thousands, the query engine will frequently ignore the indexes and just do full table scans but if you know that you frequently use BOTH of these tiny fields in searches, you can coerce the query engine into picking your index if you make a compound version by including BOTH fields. Now the combination of Gender + MaritalStatus makes the index a better choice than a full table scan.

 

[cheekybuddha]

Subqueries are inefficient in terms of query execution.

You need to qualify, as Pat did, that this is really only true for Jet/Ace.

Other RDBMS's optimise subqueries in to joins when they can, and perform [query | predicate | index] condition pushdown

 

[isladogs]

You need to qualify, as Pat did, that this is really only true for Jet/Ace.

Other RDBMS's optimise subqueries in to joins when they can, and perform [query | predicate | index] condition pushdown

100% agree with the above. I was only referring to use within Access