By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 1,026 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

More Specific Access Performance Questions

P: n/a
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?

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)??

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?

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?

What about the temporary tables created from executing queries?

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?

Thanks again to all who respond.

Bob
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"Bob Alston" <tu****************@cox.net> schreef in bericht 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?
Why 2 query's if one will do ?
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)??
That depends on your need's I would guess...
I think I would use a fulfilled date-field instead of a Boolean yes/no.
That case you can produce statistisc not only IF a request is fulfilled, but also HOW LONG it took.
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?
As long as performance is good, IMO you could use the same query's.
You should NOT use combo's with a LOT (let's say more than 100) of records in it, but limit the resultset in a case like that.
Every combo needs a query, so a LOT of combo's can slow down things a bit also.
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?

What about the temporary tables created from executing queries?

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?


I don't know if I 'read' you correctly here but ...
I would suggest to move ALL your data (tables) to move to the backend.
Only use tables in your FE if you really need to.
I believe Jet will optimize your query's (stored locally) the first time they are executed after a compact.

Are you experiencing performance-problems ?

HTH
Arno R
Nov 13 '05 #2

P: n/a
Arno R wrote:
"Bob Alston" <tu****************@cox.net> schreef in bericht 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?

Why 2 query's if one will do ?

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)??

That depends on your need's I would guess...
I think I would use a fulfilled date-field instead of a Boolean yes/no.
That case you can produce statistisc not only IF a request is fulfilled, but also HOW LONG it took.

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?

As long as performance is good, IMO you could use the same query's.
You should NOT use combo's with a LOT (let's say more than 100) of records in it, but limit the resultset in a case like that.
Every combo needs a query, so a LOT of combo's can slow down things a bit also.

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?

What about the temporary tables created from executing queries?

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?

I don't know if I 'read' you correctly here but ...
I would suggest to move ALL your data (tables) to move to the backend.
Only use tables in your FE if you really need to.
I believe Jet will optimize your query's (stored locally) the first time they are executed after a compact.

Are you experiencing performance-problems ?

HTH
Arno R

No I am not experiencing problems. Virtually all the the databases I
have built have been small number of records and small number of users -
say 10-12 users or less. I am starting to build one with a large number
of records - 10000 per year where multiple years of data will be stored.
The client is also concerned about using Access, as they have been
told (by others) that it would not handle their volume.

Bob
Nov 13 '05 #3

P: n/a
Bob Alston <tu****************@cox.net> wrote in
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
Nov 13 '05 #4

P: n/a
Bob Alston <tu****************@cox.net> wrote in
news:cS75f.17704$fE5.4369@fed1read06:
I am starting to build one with a large number
of records - 10000 per year where multiple years of data will be
stored.
The client is also concerned about using Access, as they have
been
told (by others) that it would not handle their volume.


I consider that a relatively small database.

It's only when you start talking about 100s of thousands of records
in multiple related tables that I think you have to be extra
careful.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.