Can reducing number of tables improve performance?

[Rob]

Hi,
There are several TEMP tables in my Access database that have essentially
the same fields, but are used to support different reports. Each table is
emptied and recalculated each time the corresponding report is run. Would
there be any performance advantage if I consolidated these tables into just
one TEMP table to support all the reports?

I know the process of generating a report wouldn’t change at all, but my
database would have fewer objects and the database size would be smaller…

Also, I could conceivably reduce the number of queries by using VBA to feed
specify query parameters- would this make a difference?

 

[Allen Browne]

Very little practical difference.

There might be less bloating with fewer temp tables, and therefore a smaller
file size, and therefore fewer disk sectors to read to find the data, but in
practice that kinds of perf. gain is not as significant as creating a
database that is maintainable.

Given the potential for 2 different reports that could be open at once and
trying to use the same temp table, I would consider different temp tables to
be the preferable design.

Regarding the number of queries, the count will not affect performance, but
again the maintainability question is the more important one IMHO.

Personal examples:
- I use a saved queries to feed the drop-down lists throughout the software,
e.g. all combos for selecting a client use the same query as RowSource. That
way, if we need to alter the sort-order or exclude inactive clients, there
is only one query to change and the result is consistent application-wide.

- For queries run in code, I never use saved queries. Building the SQL
string in the code reduces the dependencies between the objects in the
database, e.g. you can copy a module to another database without having to
figure out which queries to copy also.

In summary, it is more important to make a reliable and maintainable
database than to wring out every ounce of performance.

 

[Jeff Boyce]

Rob

A couple thoughts...

Your db has TEMP tables ... why? Could you use the results of a query (or
even a "chain" of queries) as your source? What is it about having a table
(even a TEMP) that satisfies some need/requirement?

You didn't indicate whether your db is single-user or multi-user. Can more
than one report be 'called' at the same time? While I'll echo Allen's
suggestion of making the db maintainable, I'd probably vote for a single
TEMP table (see comment 1 above, though), and the addition of a "report
type" field.

Regards

Jeff Boyce
<MS Office/Access MVP>

 

[Rob]

Hi Jeff,
this is a multi-user database, split into a front-end and back-end (tables
only) where the TEMP tables reside on the front end. In this particular
example, a series of queries are used to populate each TEMP table. Each
query is an aggregate query based on a separate field (a demographic
characteristic such as race or language).

Now that I think of it I could reprogram this as a UNION query (which I
didn't until recently realize could be programming into Access via SQL view)
and avoid the TEMP tables altogether... I can't see the value of a TEMP
table unless it's going to be reused (instead of being repopulated every
time). Any thoughts?

Rob

 

[Jeff Boyce]

Rob

I'd vote for the UNION query over a TEMP table, unless the performance
sucks.

Regards

Jeff Boyce
<Access MVP>