Slow report formatting

  • Thread starter Thread starter Bill R via AccessMonster.com
  • Start date Start date
B

Bill R via AccessMonster.com

I have a main report with 3 subreports. I have 3 group headers and the 3
subreports are in the details section. The parent/child link is on DMSID for
all 3 subrpts.
I have run all of the queries used in the report and the 3 subrpts. They run
very quickly. Each of the 3 sub reports displays quickly as well.
In some scenarios, the main report will return 2,000 + records. Not only is
the formatting of the report slow, I have yet to successfully display such a
large report, due to the fact that 15 or so minutes go by and I have to ctl
break the process.
If all the subreports open quickly, and all the queries run quickly, what
else could be causing the painfully slow formatting?
 
Do you have a control displaying Page of Pages? If so, get rid of at least
the Pages. You can be getting some slow down from properties such as keep
together.
 
Thanks for the suggestions. I had tried it without the & " of " & [Pages] in
the footer and that helped to display page 1 quickly, but going to the end of
the report or printing still takes just as long. I only had "Keep Together"
for the 1st group header (there are 3 group headers) which would be the
smaller number of items to sort. Is it preferable to move the "Keep Together"
further down the "food chain"? The detail section has each of the drawings
(could be over 1,000 or more) along with 3 subforms of data for each drawing.

Duane said:
Do you have a control displaying Page of Pages? If so, get rid of at least
the Pages. You can be getting some slow down from properties such as keep
together.
I have a main report with 3 subreports. I have 3 group headers and the 3
subreports are in the details section. The parent/child link is on DMSID
Click to expand...
[quoted text clipped - 12 lines]
If all the subreports open quickly, and all the queries run quickly, what
else could be causing the painfully slow formatting?
Click to expand...
Click to expand...
 
What do you mean by "drawings"? Are you displaying images or just data?

--
Duane Hookom
MS Access MVP


Bill R via AccessMonster.com said:
Thanks for the suggestions. I had tried it without the & " of " & [Pages]
in
the footer and that helped to display page 1 quickly, but going to the end
of
the report or printing still takes just as long. I only had "Keep
Together"
for the 1st group header (there are 3 group headers) which would be the
smaller number of items to sort. Is it preferable to move the "Keep
Together"
further down the "food chain"? The detail section has each of the drawings
(could be over 1,000 or more) along with 3 subforms of data for each
drawing.

Duane said:
Do you have a control displaying Page of Pages? If so, get rid of at least
the Pages. You can be getting some slow down from properties such as keep
together.
I have a main report with 3 subreports. I have 3 group headers and the 3
subreports are in the details section. The parent/child link is on DMSID
Click to expand...
[quoted text clipped - 12 lines]
If all the subreports open quickly, and all the queries run quickly,
what
else could be causing the painfully slow formatting?
Click to expand...
Click to expand...
Click to expand...
 
Duane,

Just data. The application tracks the progress of drawings through the
engineering cycle, from lay out to release. It's basically a table of
drawings (DMSID as primary key) and a table of dates (DMSID as foreign key).
There are plan, estimated, and actual dates for each step of the process.
tblDates is normalized as follows:

DMSID PhaseID DateTypeID Start Finish

where DMSID (8,000+), PhaseID (1-20), and DateTypeID(1-3, denoting plan, est,
or actual) are all long and the 2 remaining fields are Date/TIme.

I spent a lot of time normalizing the db I inherited (just awful!) and now
that I've got it just the way I want it, it takes forever to load records
into the form and even longer to print the report.

Bill

Duane said:
What do you mean by "drawings"? Are you displaying images or just data?
Thanks for the suggestions. I had tried it without the & " of " & [Pages]
in
Click to expand...
[quoted text clipped - 19 lines]
Click to expand...
 
You can trace the process by putting trace messages in the
On_Format event of each section of each report. Watch
the Format Count. Anything where the format count is
going past 2 is a candidate for some kind of layout
change.

Try to get rid of any sorting/grouping in the subreports.

(david)
 
Try David's idea to see if you reveals anything significant.

--
Duane Hookom
MS Access MVP


Bill R via AccessMonster.com said:
Duane,

Just data. The application tracks the progress of drawings through the
engineering cycle, from lay out to release. It's basically a table of
drawings (DMSID as primary key) and a table of dates (DMSID as foreign
key).
There are plan, estimated, and actual dates for each step of the process.
tblDates is normalized as follows:

DMSID PhaseID DateTypeID Start Finish

where DMSID (8,000+), PhaseID (1-20), and DateTypeID(1-3, denoting plan,
est,
or actual) are all long and the 2 remaining fields are Date/TIme.

I spent a lot of time normalizing the db I inherited (just awful!) and now
that I've got it just the way I want it, it takes forever to load records
into the form and even longer to print the report.

Bill

Duane said:
What do you mean by "drawings"? Are you displaying images or just data?
Thanks for the suggestions. I had tried it without the & " of " &
[Pages]
in
Click to expand...
[quoted text clipped - 19 lines]
what
else could be causing the painfully slow formatting?
Click to expand...
Click to expand...
Click to expand...
 
I will try it out this morning.

Thanks,

Bill
You can trace the process by putting trace messages in the
On_Format event of each section of each report. Watch
the Format Count. Anything where the format count is
going past 2 is a candidate for some kind of layout
change.

Try to get rid of any sorting/grouping in the subreports.

(david)
I have a main report with 3 subreports. I have 3 group headers and the 3
subreports are in the details section. The parent/child link is on DMSID
Click to expand...
[quoted text clipped - 12 lines]
If all the subreports open quickly, and all the queries run quickly, what
else could be causing the painfully slow formatting?
Click to expand...
Click to expand...
 
David,

Heres a "debug.print" of 1 full cycle of my report formatting events. Should
I consider "de-normalizing" for the dates subreport, by creating a temp table
for all dates in the on open event of the report? I suspect that might be the
culprit.

Header - 1 - 7:14:18 AM
SRptFilter - 1 - 7:14:18 AM
PageHeader - 1 - 7:14:25 AM
WEHeader - 1 - 7:14:25 AM
IPTHeader - 1 - 7:14:25 AM
DMSIDHeader - 1 - 7:14:25 AM
Detail - 1 - 7:14:25 AM
DatesSRptHeader - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
SRptEngnrHeader - 1 - 7:14:25 AM
SRptEngnrDetail - 1 - 7:14:25 AM
SRptEngnrDetail - 1 - 7:14:25 AM
SRptEngnrDetail - 1 - 7:14:25 AM
SRptRemarksDetail - 1 - 7:14:25 AM
DMSIDHeader - 1 - 7:14:25 AM
Detail - 1 - 7:14:25 AM
DatesSRptHeader - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
DatesSRptDetail - 1 - 7:14:25 AM
SRptEngnrHeader - 1 - 7:14:25 AM
SRptEngnrDetail - 1 - 7:14:25 AM
SRptEngnrDetail - 1 - 7:14:25 AM
SRptEngnrDetail - 1 - 7:14:25 AM
SRptRemarksDetail - 1 - 7:14:25 AM
DMSIDHeader - 1 - 7:14:25 AM
Detail - 1 - 7:14:26 AM
DatesSRptHeader - 1 - 7:14:26 AM
DatesSRptDetail - 1 - 7:14:27 AM
DatesSRptDetail - 1 - 7:14:27 AM
DatesSRptDetail - 1 - 7:14:27 AM
DatesSRptDetail - 1 - 7:14:27 AM
DatesSRptDetail - 1 - 7:14:27 AM
DatesSRptDetail - 1 - 7:14:27 AM
DatesSRptDetail - 1 - 7:14:27 AM
DatesSRptDetail - 1 - 7:14:27 AM
SRptEngnrHeader - 1 - 7:14:27 AM
SRptEngnrDetail - 1 - 7:14:27 AM
SRptEngnrDetail - 1 - 7:14:27 AM
SRptEngnrDetail - 1 - 7:14:27 AM
SRptRemarksDetail - 1 - 7:14:27 AM
DMSIDHeader - 1 - 7:14:27 AM
Detail - 1 - 7:14:28 AM
DatesSRptHeader - 1 - 7:14:28 AM
DatesSRptDetail - 1 - 7:14:28 AM
DatesSRptDetail - 1 - 7:14:28 AM
DatesSRptDetail - 1 - 7:14:28 AM
DatesSRptDetail - 1 - 7:14:28 AM
DatesSRptDetail - 1 - 7:14:28 AM
DatesSRptDetail - 1 - 7:14:28 AM
DatesSRptDetail - 1 - 7:14:28 AM
DatesSRptDetail - 1 - 7:14:28 AM
SRptEngnrHeader - 1 - 7:14:28 AM
SRptEngnrDetail - 1 - 7:14:28 AM
SRptEngnrDetail - 1 - 7:14:28 AM
SRptEngnrDetail - 1 - 7:14:28 AM
SRptRemarksDetail - 1 - 7:14:28 AM
DMSIDHeader - 1 - 7:14:28 AM
Detail - 1 - 7:14:29 AM
DatesSRptHeader - 1 - 7:14:29 AM
DatesSRptDetail - 1 - 7:14:29 AM
DatesSRptDetail - 1 - 7:14:29 AM
DatesSRptDetail - 1 - 7:14:29 AM
SRptEngnrHeader - 1 - 7:14:29 AM
SRptEngnrDetail - 1 - 7:14:29 AM
SRptEngnrDetail - 1 - 7:14:29 AM
SRptEngnrDetail - 1 - 7:14:29 AM
SRptRemarksDetail - 1 - 7:14:29 AM
Footer - 1 - 7:14:29 AM
 
FYI,

It takes 7 to 8 seconds to move from one page to the next in Print Preview.
 
I see that it takes 7 seconds to go from/to
SRptFilter - 1 - 7:14:18 AM
PageHeader - 1 - 7:14:25 AM
Click to expand...

I don't recognise SRptFilter - what event is that? I don't
see any other events for that?

(david)
 
Back
Top