DB Optimization Questions on reducing Table/Query count

[cefrancke]

I'm trying to do some optimizations on my Access DB.
After reading on table/query counts, I decided to examine all queries
and reuse common queries instead of re-creating the same query with
another name.

I have found a number of queries used in forms that could easily
(apparently) be used in a corresponding report. A good thing, of
course.

Questions:
1. Combo boxes:
I read about using defined queries instead of SQL in combo boxes
(Access compiles the query the first time the form is opened, as
opposed to compiling every time the SQL statement is used. Please
confirm)
I assume refering directly to a table, would be efficient as well, for
example in a simple combo lookup, where just the ID (bound column) and
a description field (display list) are necessary.
However, what if I want the description field to be sorted, should I
create a query just for that, or use SQL or is there a way to have the
table sort in the combo automatically on the display field?
I have many different "pick box" combos where I need the display field
sorted and this could imply that I may have to create a stored query
for each combo, if this is most efficient.

2. Reports
Is it faster to allow the report controls/code do as many calculations
as possible or let the
underlying query do all the calculations? My intuition tells me to let
the report do it if it can because the query will return filtered data
and the report will do the calculations on the smaller filtered
recordset, as opposed to the query doing calculations on all the data
and the report filtering the results. I'm assuming there is some order
to the query event, report filter event, and report calculation events.


Thanks in advance for any insight.

Christopher

 

[Douglas J. Steele]

I don't think you'll notice any real difference with your combo boxes (and,
for what it's worth, even if you put SQL there, Access is actually going to
store a hidden query under the covers).

I also don't think you'll notice significant difference with your reports
either. Access returns the rows before it does the calculations, so your
concern about how many function calls there will be isn't an issue.
Personally, I like putting the calculations in the queries, because I can
use the queries elsewhere if necessary.

 

[Larry Linson]

It's not always a good idea to "make the most use of Queries" by using the
same query for a Form and a Report. On the surface, that seems to be A Good
Idea because it will save the storage space for another Query, and because
if you change the Query, both the Form and Report will automatically include
the change. But, at some later point, if you modify the Query because of a
change to the Form, and the Report suddenly has problems, you may think it
was A Bad Idea.

And, by the way, the storage space for an extra Query, with the larger size
of Access since Access 2000 and the significantly lower cost of computer
memory and disk is not much of an issue.

The only issue is whether you are likely to make exactly the same changes on
the Form and Report that share the Query, or whether you may make different
changes... it's a trade-off that only you can make, based on your
application and your environment. My personal practice is, generally, to
use separate Queries for each object that has a Record Source, and spend a
little extra time changing them if the objects _do_ both require the same
changes. YMMV.

Larry Linson
Microsoft Access MVP