Speed up report performance

Jana

Hi!

I have an Access 97 report based on tables linked to an SQL back end.
The report has 5 subreports in it (unfortunately unavoidable due to the
nature of the report) and performance is quite slow. The query runs
quickly and the report displays the first page in a short amount of
time, but takes quite a while to format each page. This means it takes
a long time to print the report (up to 50 mins to print around 360
pages!) as it formats each page.

My main report has some controls that hide or change captions depending
on the values of other controls on the report, as well as one level of
grouping (both header and footers for that grouping). I have tried to
minimize the number of changes that are done on the fly in the report
and I have experimented with the 'Keep Together' options of my
grouping. My subreports link to the main report by a key that is a
combination of two fields (calculated in the query for the main
report), but is not indexed since it's calculated at run-time. We only
have read access to the SQL back end (it's part of a proprietary
program).

My question is this: Would it be faster to write the data to temp
tables, thereby allowing me to create an index on my key field, and
then base the report/sub reports on local tables rather than linked
tables? What about putting just the objects required for this report
in it's own front end? Are there any other tricks I could use to
improve the formatting speed? This is a complex report, but certainly
not the MOST complex thing I've ever written, so I'm at a loss as to
how to make it better.

Thanks in advance,
Jana

Jan 18 '07 #1

Marshall Barton

Jana wrote:

>I have an Access 97 report based on tables linked to an SQL back end.
The report has 5 subreports in it (unfortunately unavoidable due to the
nature of the report) and performance is quite slow. The query runs
quickly and the report displays the first page in a short amount of
time, but takes quite a while to format each page. This means it takes
a long time to print the report (up to 50 mins to print around 360
pages!) as it formats each page.

My main report has some controls that hide or change captions depending
on the values of other controls on the report, as well as one level of
grouping (both header and footers for that grouping). I have tried to
minimize the number of changes that are done on the fly in the report
and I have experimented with the 'Keep Together' options of my
grouping. My subreports link to the main report by a key that is a
combination of two fields (calculated in the query for the main
report), but is not indexed since it's calculated at run-time. We only
have read access to the SQL back end (it's part of a proprietary
program).

My question is this: Would it be faster to write the data to temp
tables, thereby allowing me to create an index on my key field, and
then base the report/sub reports on local tables rather than linked
tables? What about putting just the objects required for this report
in it's own front end? Are there any other tricks I could use to
improve the formatting speed? This is a complex report, but certainly
not the MOST complex thing I've ever written, so I'm at a loss as to
how to make it better.

The most important performance factor is to index the
linking fields and the fields with criteria in the subreport
record source queries. If you have to use temporary tables
to do that, then I suggest that you put the temp tables in a
temporary mdb on the front end machine and link to them.

Of lesser importance is to avoid using Pages (e.g. a text
box with: =Page & " of " & Pages) and, if feasible, Whole
Group KeepTogether that will usually be forced to the start
of a page. Also make sure the subreport record source
queries do not have an Order By clause.

--
Marsh

Jan 18 '07 #2

Jana

Marshall:
Thanks for the input. I'll set up temp tables for my data on the front
end and index the key. For the record, I don't have any page numbering
or any Whole Group keep together settings, but I do have the records
sorted on my subreport (which is required for the report).

I will set up my temp tables and see how that does for me.

Thanks again!
Jana
Marshall Barton wrote:

Jana wrote:

I have an Access 97 report based on tables linked to an SQL back end.
The report has 5 subreports in it (unfortunately unavoidable due to the
nature of the report) and performance is quite slow. The query runs
quickly and the report displays the first page in a short amount of
time, but takes quite a while to format each page. This means it takes
a long time to print the report (up to 50 mins to print around 360
pages!) as it formats each page.

My main report has some controls that hide or change captions depending
on the values of other controls on the report, as well as one level of
grouping (both header and footers for that grouping). I have tried to
minimize the number of changes that are done on the fly in the report
and I have experimented with the 'Keep Together' options of my
grouping. My subreports link to the main report by a key that is a
combination of two fields (calculated in the query for the main
report), but is not indexed since it's calculated at run-time. We only
have read access to the SQL back end (it's part of a proprietary
program).

My question is this: Would it be faster to write the data to temp
tables, thereby allowing me to create an index on my key field, and
then base the report/sub reports on local tables rather than linked
tables? What about putting just the objects required for this report
in it's own front end? Are there any other tricks I could use to
improve the formatting speed? This is a complex report, but certainly
not the MOST complex thing I've ever written, so I'm at a loss as to
how to make it better.

The most important performance factor is to index the
linking fields and the fields with criteria in the subreport
record source queries. If you have to use temporary tables
to do that, then I suggest that you put the temp tables in a
temporary mdb on the front end machine and link to them.

Of lesser importance is to avoid using Pages (e.g. a text
box with: =Page & " of " & Pages) and, if feasible, Whole
Group KeepTogether that will usually be forced to the start
of a page. Also make sure the subreport record source
queries do not have an Order By clause.

--
Marsh

Jan 19 '07 #3

Marshall Barton

Sorting a report via Sorting and Grouping is normal. The
point I was trying to make is that sorting in a report's
**record source query** is a waste of time. Just make sure
the record source queries do not have an ORDER BY clause.

Putting temp tables in your front end MDB file might be a
major clause of bloat. This in turn forces you to have a
robust backup procedure so you can be safe during the more
frequent compact operations you will need to perform.

For details of how to use a temporary database to hold the
temp tables, see:
http://www.granite.ab.ca/access/temptables.htm

You may also want to browse Tony's Performace FAQ at:
http://www.granite.ab.ca/access/performancefaq.htm
--
Marsh
Jana wrote:

>Thanks for the input. I'll set up temp tables for my data on the front
end and index the key. For the record, I don't have any page numbering
or any Whole Group keep together settings, but I do have the records
sorted on my subreport (which is required for the report).

I will set up my temp tables and see how that does for me.

Marshall Barton wrote:

>Jana wrote:

>I have an Access 97 report based on tables linked to an SQL back end.
The report has 5 subreports in it (unfortunately unavoidable due to the
nature of the report) and performance is quite slow. The query runs
quickly and the report displays the first page in a short amount of
time, but takes quite a while to format each page. This means it takes
a long time to print the report (up to 50 mins to print around 360
pages!) as it formats each page.

My main report has some controls that hide or change captions depending
on the values of other controls on the report, as well as one level of
grouping (both header and footers for that grouping). I have tried to
minimize the number of changes that are done on the fly in the report
and I have experimented with the 'Keep Together' options of my
grouping. My subreports link to the main report by a key that is a
combination of two fields (calculated in the query for the main
report), but is not indexed since it's calculated at run-time. We only
have read access to the SQL back end (it's part of a proprietary
program).

My question is this: Would it be faster to write the data to temp
tables, thereby allowing me to create an index on my key field, and
then base the report/sub reports on local tables rather than linked
tables? What about putting just the objects required for this report
in it's own front end? Are there any other tricks I could use to
improve the formatting speed? This is a complex report, but certainly
not the MOST complex thing I've ever written, so I'm at a loss as to
how to make it better.

The most important performance factor is to index the
linking fields and the fields with criteria in the subreport
record source queries. If you have to use temporary tables
to do that, then I suggest that you put the temp tables in a
temporary mdb on the front end machine and link to them.

Of lesser importance is to avoid using Pages (e.g. a text
box with: =Page & " of " & Pages) and, if feasible, Whole
Group KeepTogether that will usually be forced to the start
of a page. Also make sure the subreport record source
queries do not have an Order By clause.

Jan 19 '07 #4

Jana

Marshall:

Ahhhh....I missed that when I first read your response with respect to
a temp db rather than just temp tables in my front end. I'll also
double check my queries for Order By clauses and delete as needed.
Thanks for the clarification, I'll check out the links you've given me.

Thanks much,
Jana
Marshall Barton wrote:

Sorting a report via Sorting and Grouping is normal. The
point I was trying to make is that sorting in a report's
**record source query** is a waste of time. Just make sure
the record source queries do not have an ORDER BY clause.

Putting temp tables in your front end MDB file might be a
major clause of bloat. This in turn forces you to have a
robust backup procedure so you can be safe during the more
frequent compact operations you will need to perform.

For details of how to use a temporary database to hold the
temp tables, see:
http://www.granite.ab.ca/access/temptables.htm

You may also want to browse Tony's Performace FAQ at:
http://www.granite.ab.ca/access/performancefaq.htm
--
Marsh
Jana wrote:

Thanks for the input. I'll set up temp tables for my data on the front
end and index the key. For the record, I don't have any page numbering
or any Whole Group keep together settings, but I do have the records
sorted on my subreport (which is required for the report).

I will set up my temp tables and see how that does for me.

Marshall Barton wrote:

Jana wrote:
I have an Access 97 report based on tables linked to an SQL back end.
The report has 5 subreports in it (unfortunately unavoidable due to the
nature of the report) and performance is quite slow. The query runs
quickly and the report displays the first page in a short amount of
time, but takes quite a while to format each page. This means it takes
a long time to print the report (up to 50 mins to print around 360
pages!) as it formats each page.

My main report has some controls that hide or change captions depending
on the values of other controls on the report, as well as one level of
grouping (both header and footers for that grouping). I have tried to
minimize the number of changes that are done on the fly in the report
and I have experimented with the 'Keep Together' options of my
grouping. My subreports link to the main report by a key that is a
combination of two fields (calculated in the query for the main
report), but is not indexed since it's calculated at run-time. We only
have read access to the SQL back end (it's part of a proprietary
program).

My question is this: Would it be faster to write the data to temp
tables, thereby allowing me to create an index on my key field, and
then base the report/sub reports on local tables rather than linked
tables? What about putting just the objects required for this report
in it's own front end? Are there any other tricks I could use to
improve the formatting speed? This is a complex report, but certainly
not the MOST complex thing I've ever written, so I'm at a loss as to
how to make it better.
The most important performance factor is to index the
linking fields and the fields with criteria in the subreport
record source queries. If you have to use temporary tables
to do that, then I suggest that you put the temp tables in a
temporary mdb on the front end machine and link to them.

Of lesser importance is to avoid using Pages (e.g. a text
box with: =Page & " of " & Pages) and, if feasible, Whole
Group KeepTogether that will usually be forced to the start
of a page. Also make sure the subreport record source
queries do not have an Order By clause.

Jan 19 '07 #5