Advice on improving report processing speed

[homer]

I am looking for some advices/best practice know-how’s on improving report
processing speed.

I have found good ones from the existing posts on this forum.

Basically, sometimes the report seems just timeout. It is a single page
report with a lot of sub reports/sessions in it. Many function calls to code
modules, many business rules, but all database calls to sql2k are in stored
procedures.

It is in access 97, and could have as many as 50 users concurrently
previewing/printing it.

How do you find its bottleneck? Does newer version of access do a better job
in this regard?
Thanks!

 

[Duane Hookom]

We don't know which tips you have "found good ones from the existing posts
on this forum". Any replies may be duplicates to what you already know.
However these are a few speed bumps:
-using Page of Pages
-using DSum() or DCount() or ...DAnything()
-Opening recordsets in code
-queries that join a local/JET table with a linked SQL table
-joins or Link Master/Child fields that are not indexed

I have often resorted to creating local temporary tables for a report. This
is usually done with code that creates a temporary MDB with required tables.
Then append records into the temporary mdb tables and base your reports on
these tables linked from your temp mdb.