Hi,
You should not see any difference in the case of an inner join.
Timings is to be taken into context. It is like some people who think
that using a saved query, with a saved execution plan, is preferable to use
a not-compiled query. They are just wrong, or, to be more precise, not
always right. If the tables are small, the query plan will be a table scan
(since it takes times to load indexes, for small table, loading indexes
would be too expensive and a simple table scan occur). If we use the same
compiled query when the tables get large, the table scan is not appropriate
any more, but the query NOT being "recompiled", the ineffective query plan
continue to be used. So, when you compile the queries, or when you proceed
with timings comparisons between different possible solutions, as you asked,
it is appropriate to be "as close as possible" as the real case: small
tables, large tables, all the fields in the SELECT clause are involved in
the comparison (index covered query: just loading the index, NOT the data,
is possible), or not (some field in the SELECT clause are NOT involved in
the indexes) are all pertinent details. That is why SQL is efficient: it
produces different strategies accordingly to the real case at hand.... It is
not like a standard program where we write ONE way to reach the goal. Since
multiple way to reach the goal are analyzed, the test should be done with
the details and the data size that can be expected in production. Otherwise,
"compiled query", or "here, solution A is faster than solution B" may just
be not the appropriate somewhere else.
Hoping it may help,
Vanderghast, Access MVP
