Left Join vs Inner Join

[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!

 

[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