Multiple Sub, Sub-Reports - Report Performance

[Programmer - wannaB]

Access2003 running local on Dual core XP Pro SP2. Medium size Access DB.
Report consists of multiple queries and subQueries, and 4 subReports, one of
which contains 10 more subReports. I can run the Queries all instantly, but
when running the report, or even the one subReports with 10 subReports, this
takes progressive longer and the status bar message say "Formatting
Report........." Final report on 467 pages can take 25 minutes just to
preview, and another 30 minutes to print.

Is there anything I can do to improve the formatting process of reports like
this??
Thanks so much for any replies...

 

[Golfinray]

I'm running an 1800 page report now using 20 queries and 10 or 12 subforms
and I have been printing for a week. I can get 50 pages at a time. If I try
anymore, it hangs. 50 pages takes about an hour and a half. I'm also using a
new dual core.

 

[NetworkTrade]

I really can't help too much - maybe an MVPer has insight into the
fundamentals of how the report generates.... one thing that stands out is
you say all the queries run instantly; but one would think that some of these
subreports are linked to the main report in the traditional parent/child
one/many...meaning the subreport must rerun with each main report record; so
that query doesn't run once, it runs repeatedly.....

If the subreports happen to be really stand alone and not in a relationship
to the main report records - then you could think about generating them as
separate stand alone reports and stringing together the opening of several
reports. It can be crude but might work... particularly if the emphasis is
on printing - because you can print one after the other...and no one knows
that they are actually separate reports...

Not particularly helpful....sorry,

 

[Tony Toews [MVP]]

Access2003 running local on Dual core XP Pro SP2. Medium size Access DB.
Report consists of multiple queries and subQueries, and 4 subReports, one of
which contains 10 more subReports. I can run the Queries all instantly, but
when running the report, or even the one subReports with 10 subReports, this
takes progressive longer and the status bar message say "Formatting
Report........." Final report on 467 pages can take 25 minutes just to
preview, and another 30 minutes to print.

Basically put many subreports on reports with many pages Access
handles very poorly in a split FE/BE situation.

Is there anything I can do to improve the formatting process of reports like
this??

Dump the subreports. See the last two paragraphs at Performance in
Microsoft Access is worse after splitting - My personal experience
http://www.granite.ab.ca/access/splitapp/performance.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/