Reuse satic subreport data?

  • Thread starter Thread starter Green Biro
  • Start date Start date
G

Green Biro

I have designed a report based on student.

For each student I need a number of sub reports run, some of which return
more than one record.

This works fine except for the fact that it is very slow, presumably because
the sub reports are being run once for each student and some of them are
quite complicated involving calculated fields and subqueries.

This seems inefficient to me. Is there some way I can get the sub report
queries to run just once at the outset and somehow 'cache' the data so that
a filtered version of it can be shown for each student.


Thanks

GB
 
Use a query to make a temporary table or append the data and call the report
from that table.When the report closes, you can delete the temp table or the
data therein. Make sure you compact the database regularly so it doesn't
bloat.
 
Consider if you can join all the necessary tables in one query to use as Row
Source, and if you can use "hide duplicates" and "can shrink / can grow" to
present the same data. I'm not sure how much speed you'd gain, but it's
worth a try, I suspect.

If you follow Arvin's advice, you may benefit from looking at MVP Tony
Toews' site about using temporary tables in a temporary database to avoid
frequent compact and repair.

Larry Linson
Microsoft Office Access MVP
 
Hi GB,

I would try to identify the bottleneck, to see if a particular query or
subquery can be re-written in a more efficient manner. You might want to have
a look at this document:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

Many of the tips are useful even for single-user applications. Make sure to
check out the two sections:

Use indexes
and
Use JETSHOWPLAN

The JetShowPlan capability will not help you optimize subqueries, but can
certainly help reveal table scans that may be occuring on large tables.
(Don't worry too much about table scans performed against relativly small
tables, as this might be the most efficient query plan).

Also, do any of the queries that support your report (or subreport) include
a domain aggregrate function?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Thank you Arvin.

In the end I went for your solution as it's the simplest to implement. I
don't need to delete the table as a 'maketable' query emcompasses that
action anyway.

I haven't checked for bloating but if that does happen I can handle it
manually when required.

Thanks again

GB
 
Thanks for your reply.

I thought about that but the queries are quite complex with sub queries and
calculated fields and to join them all together and filter out duplicates
would be a bit of a nightmare. I've thus gone for the simpler temp table
solution suggested by Arvin.

Thanks again for your interest.

GB
 
Thank you for the reply.

I'll look into this paper to increase my knowledge but I think that in my
case I know where the bottleneck is caused (viz the need to keep rerunning
the queries).

For this relatively small scale project, it makes more sense just to create
a temp table as per Arvin's suggestion.

Thanks again for your interest.

GB
 
Back
Top