Reuse satic subreport data?
in [General]
|
Prev: What does this mean: MSysCompactError
Next: test
From: Green Biro on 3 Nov 2009 19:19 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 "Larry Linson" <bouncer(a)localhost.not> wrote in message news:eBfrdelWKHA.844(a)TK2MSFTNGP05.phx.gbl... > 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 > > "Green Biro" <dummygreenbiro(a)breenbiro.com> wrote in message > news:39XGm.12277$Cq1.939(a)newsfe12.ams2... >>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 >> >> >> > > >
From: Green Biro on 3 Nov 2009 19:23
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 "Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message news:0C89B00F-E3A5-483D-BAD2-D8E23D9FB948(a)microsoft.com... > 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/ > __________________________________________ > > "Green Biro" wrote: > >> 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 |