Slow report formatting

[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?

 

[Duane Hookom]

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.

 

[Bill R via AccessMonster.com]

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.

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

[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?

 

[Duane Hookom]

What do you mean by "drawings"? Are you displaying images or just data?

--
Duane Hookom
MS Access MVP

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.

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

[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?

 

[Bill R via AccessMonster.com]

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

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

[quoted text clipped - 19 lines]

 

[david epsom dot com dot au]

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)

 

[Duane Hookom]

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

--
Duane Hookom
MS Access MVP

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

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

[quoted text clipped - 19 lines]

what
else could be causing the painfully slow formatting?

 

[Bill R via AccessMonster.com]

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

[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?

 

[Bill R via AccessMonster.com]

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

 

[Bill R via AccessMonster.com]

The layout is

Section - FormatCount - TimeStamp (Now())

 

[Bill R via AccessMonster.com]

FYI,

It takes 7 to 8 seconds to move from one page to the next in Print Preview.

 

[david epsom dot com dot au]

I see that it takes 7 seconds to go from/to

SRptFilter - 1 - 7:14:18 AM
PageHeader - 1 - 7:14:25 AM

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

(david)