Reuse satic subreport data?
in [General]
|
Prev: What does this mean: MSysCompactError
Next: test
From: Green Biro on 31 Oct 2009 09:42 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: Arvin Meyer [MVP] on 31 Oct 2009 12:40 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. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "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: Larry Linson on 31 Oct 2009 14:26 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: Tom Wickerath AOS168b AT comcast DOT on 2 Nov 2009 01:04 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
From: Green Biro on 3 Nov 2009 19:16
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 "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message news:eA%23d9CkWKHA.5208(a)TK2MSFTNGP05.phx.gbl... > 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. > -- > Arvin Meyer, MCP, MVP > http://www.datastrat.com > http://www.mvps.org/access > http://www.accessmvp.com > > > "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 >> >> >> > > |