> Note that Roy's reply explicitly depends on indexes being available.
Yes, this is one of the reasons indexing is usually recommended for columns you wish to filter or sort on, and also why it is particularly important in a FE/BE setup. See #119 and #120 in the link I gave.
Putting an index on the foreign key, though, isn't necessary, as that is done by Jet when creating the relationship, though if you join on non-key/non-indexed columns, indices on both sides will be beneficial.
> If you have no indexes and it is an Access database with the tables on a remote machine (regardless of where the form/report/query is located), Access MUST pull in the whole table.
Gee, you sound like you don't like indexing, and/or don't expect people to use it
> Without the query being stored, you will read the whole table (because Access doesn't have a clue beforehand on the best method to use whatever the table has.)
Nope, Jet will optimize also dynamic SQL. Fact is, dynamic SQL can even be faster than precompiled querydefs, if there's been a while since they were compiled.
Try test whether it loads the whole table or not, try the following simple test in a form, remove the recordsource, and have one button for filtered and one button for All, with the code similar to the following
Me.RecordSource = "SELECT <the columns> FROM myTable"
and
Me.RecordSource = "SELECT <the columns> FROM myTable WHERE <SomeINdexedField> = SomeValue"
To diminish the importance of buffers and caches, one might at least close Access between each try. You should see significant difference between retrieving a couple of hundred records and retrieving the whole table.
I don't know how to time the opening of forms, but the filtered version, which gave 100 records, used a bit less than one second, the non filtered version, about five seconds. There is a total of 360 000 records).
Had your assertion been correct, that without a stored query, it would read the whole table, then I doubt anyone would be using dynamic SQL when opening recordsets, but it turns out the Optimizer does a fine job also when you don't have precompiled querydefs.
It is very easy to verify, try a dynamic string of the type
SELECT <the columns>
FROM myTable WHERE
SomeINdexedField = SomeValue
vs
SELECT <the columns>
FROM myTable WHERE
Some_NOT_idexedField = SomeValue
With my setup, and 360 000 records, the Time function is enough to tell the difference.
dt = time
set rs = currentdb.openrecordset(theSQL)
if rs.recordcount>0 then
rs.movelast
debug.print rs.recordcount
end if
debug.print format$(time-dt, "hh:nn")
This takes 26 seconds on the non indexed table, and I was finally able to make the query on the indexed table return 1 second one or two times, by repeatedly hitting F5 several times, but the Time function had problems recording this efficiency
Even without traversing the recordset, it uses 21 seconds just to open a recordset on the non indexed table with the criterion.
Opening the table and looping to find the same records, takes 28 seconds, using .FindFirst/.FindNext takes the same (28 seconds)
This again stresses the need to do proper indexing!
Another interesting thing, which is also indicated by Pat Hartman here
http://www.access-programmers.co.uk/forums/showthread.php?t=70952, you might even experience that if you join a local table with a ODBC linked table, Jet will only bring the relevant records over. Jet will at least try to optimize, so that it doesn't have to bring it all over.
