where clause

[inungh]

I have a table which has millions records.

I understand that I need put WHERE clause which are index fields to
limit records and improve performance.

I would like to know does Access really bring the records only to
front end application depends on the WHERE clause or bring millions
records to front end application and doing analyze at front end
application?

If yes, it should help perfromance the stack queries like one query
use another query as source if previous query to limit recrods.

your information and help is great appreciated,

 

[vanderghast]

It is said that Jet bring the required INDEXes locally, if there are any,
and only if required, bring the values from the records. If you have no
where clause, indeed, the million of records could ***eventually*** be
brought, ASYNCHRONOUSLY, to the front end: you will be able to see the first
ones almost instantaneously, and the other records are fetched by another
working thread not necessary locking the User Interface... but if you make a
move to last record, that may then take some time. As far as queries
embedded in queries, I suspect that depends on the nature of the queries,
mainly to the join they use, if they use any.



A concept that may help to understand is to note that Jet evaluates the
SELECT clause on a just-in-time strategy:

SELECT id,
MyVBAfunction( id ) AS computedStuff
FROM ...



It will consume execution time for the vba function only if the record is
'visible' to the application... and won't cache it. It will be re-executed
if the record become visible once more (through a recordset active record
pointing to it, or through a form now displaying the record a second time),
so I suspect Jet also fetches the values of the record only on a strict
just-in-time strategy, so you will very unlikely have all your million
records 'locally', even if it is possible, if they are part of a correlated
sub-query, as example. Move the VBA function to the WHERE clause, and now,
dependant of its position in the WHERE clause, that function ***could*** be
executed for each and every record (unless the AND-ed construction has
already dismissed the record... note that Jet seems to evaluate the WHERE
clause from left to right, so add the 'expensive' tests at the end of the
AND sequences).


So, in short, sorry for the savorless answer, but "it depends".

 

[inungh]

It is said that Jet bring the required INDEXes locally, if there are any,
and only if required, bring the values from the records. If you have no
where clause, indeed, the million of records could ***eventually*** be
brought, ASYNCHRONOUSLY, to the front end: you will be able to see the first
ones almost instantaneously, and the other records are fetched by another
working thread not necessary locking the User Interface... but if you make a
move to last record, that may then take some time. As far as queries
embedded in queries, I suspect that depends on the nature of the queries,
mainly to the join they use, if they use any.

A concept that may help to understand is  to note that Jet evaluates the
SELECT clause on a just-in-time strategy:

SELECT id,
    MyVBAfunction( id ) AS computedStuff
FROM ...

It will consume execution time for the vba function only if the record is
'visible'  to the application... and won't cache it. It will be re-executed
if the record become visible once more (through a recordset active record
pointing to it, or through a form now displaying the record a second time),
so I suspect Jet also fetches the values of the record only on a strict
just-in-time strategy, so you will very unlikely have all your million
records 'locally', even if it is possible, if they are part of a correlated
sub-query, as example. Move the VBA function to the WHERE clause, and now,
dependant of its position in the WHERE clause, that function ***could*** be
executed for each and every record (unless the AND-ed construction has
already dismissed the record... note that Jet seems to evaluate the WHERE
clause from left to right, so add the 'expensive' tests at the end of the
AND sequences).

So, in short, sorry for the savorless answer, but "it depends".










- Show quoted text -

Thanks millions for the information and helping,