Re: More Specific Access Performance Questions

Bob Alston <tulsaalstonsNOSPAM@xxxxxxx> wrote in
YO35f.17698$fE5.16987@fed1read06:">news:YO35f.17698$fE5.16987@fed1read06:

> Some more, rather specific Access performance questions. IN a
> split front-end & back-end Access/Jet ONLY LAN situation, and with
> all query criteria fields and join fields indexed:
>
> 1. Is is good form to have a single query with base table with
> criteria joined to a related table - all in one query? Or should
> I do a two-step, first query does selection of main table and then
> join with other table?

The Jet query optimizer will treat the two entirely equivalently. It
looks at what you've asked for and figures out which thing to do
first so as to reduce the amount of data operated on to the smallest
dataset possible, and it does that as quickly as it can.

For instance, say you have a join of two tables and a criterion on
one table of the join. If table A is 10 records and table B is 10K
records, and the criterion is on table B, it's likely that the join
will be done first, and then the criterion applied, since the number
of records will be limited more by the join than by the criterion.

Now, if instead, the criterion is on table A, it will filter table
A, and then do the join, because table A is so much smaller that
limiting it will automatically reduced the number of records that
need to be joined.

Now, I don't know how smart Jet is with this. I don't know if it's
smart enough to check the indexes involved in the join and in the
criteria to see which is going to be more efficient (limits on a
sparsely populated index in a large table are going to be less
efficient than joins on a non-sparsely populated index).

But you can see this by turning on SHOWPLAN. I'm pretty sure the
instructions for turning it on are on the Access Web. Then you can
examine how the query is optimized (though SHOWPLAN does not give
any information on subqueries).

> 2. I have a table with multiple years data in it. Each record
> has the year it was entered and an indicator if the request was
> fulfilled. I expect to have 10k records a year with multiple
> years of data in the table. Most are rather quickly fulfilled. I
> need to produce a list of all requests that are not yet fulfilled.
> Is it OK to have a simple criteria of fulfillment status = false
> or should I combine that with the year (generally it would be the
> current year)??

This is a difficult question. Conventional wisdom is that Boolean
fields (and any other field with few unique values) do not benefit
from indexing, but I have found that they *do* benefit from it.

I question whether storing a Boolean value is the right way to do
it. Surely there's a date when the request is fulfilled, and that
could serve as the indicator of whether a request is closed. The
field would be Null until it was fullfilled, at which time a date
would be filled in. Then you'd be doing something very simple,
checking for IsNull(). I have a suspicion that this is faster than
selecting on a Boolean value, even when both fields are indexed, but
you'd have to check to find out.

> 3. I tend to use a lot of drop-down list boxes with things such as
> race code, zip codes, etc. I usually use a single query for
> display of the drop-down list box as well as in the Form that
> updates them. Am I better off to create a second query for
> display only and then define it as a "snapshot" type?

I don't often use saved queries for either combo box rowsources or
for form recordsources. I only do so when there are complexities
that would benefit from have a query that abstracts some kind of
structure.

I used to do saved queries for these things, thinking that it
improved performance, but it never seemed to me to be enough to
justify having all those saved queries. Certainly for lookup tables,
which are going to have very few rows, there's going to be no real
benefit from saved queries in terms of performance.

> 4. Last question (for now). IN a front-end back-end situation,
> is there any local caching of completed queries? For example,
> after the first time one of my drop down lists was extracted, will
> it be stored locally for reuse?

Most definitely for combo boxes, but only if the rowsource is
static, and only if the form remains open.

> What about the temporary tables created from executing queries?

What about them?

> And if stored locally, does that make subsequent joins with other
> tables slower since one table is at the front end and the joined
> table is at the back end?

Many advocate using local tables for lookups, but I've never felt
it's worth it. I use temp tables for temporary data used in
calculations or when the SQL that returns the set of records
corresponding to the temp table is just way too slow to usable.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
.