472,796 Members | 2,250 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,796 software developers and data experts.

Speed up report performance

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
4 7077
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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Tony Clarke | last post by:
Hi, Background: I have written a Call Logging and Reporting System using PHP that integrates with an existing MS Access Database. The information in the database is all held in flat tables...
16
by: Shailesh Humbad | last post by:
I posted this article I wrote a couple weeks ago, but I've completely updated it, with new sections on the performance of 'bursting' GetRows and GetString. If you're interested: ...
2
by: Wouter | last post by:
Hi, I was wandering. Does the query speed also depend on the colums that you don't use in the query (so not in the where and not in the select part). I have a lange unused backup field in...
30
by: Mike Cox | last post by:
Hi. I recently ran a benchmark against two simple programs, one written in Java and the other in C++. The both accomplish the same thing, outputting "Hello World" on my screen. The C++ program...
4
by: Richard Hollenbeck | last post by:
The following query takes about one second to execute with less than 1,000 records, but the report that's based on it takes from 15-30 seconds to format and display. That's frustrating for both me...
3
by: Mike Kelly | last post by:
Hi. I've built a page using standard ASP.NET 2.0 features and when I upload a large file (>20MB) to our intranet server, I get a paltry 100KB/s on our 100Mb/s LAN. Simply copying the file, I get...
2
by: jphelan | last post by:
Ever since I successfully applied some techniques for increasing the speed of my 17 meg. Application; it has only made me hunger for more. First, let me list what I have done so far: 1. Split...
45
by: charles.lobo | last post by:
Hi, I have recently begun using templates in C++ and have found it to be quite useful. However, hearing stories of code bloat and assorted problems I decided to write a couple of small programs...
0
by: MattGaff | last post by:
I have a Web server running Reporting Services which has (all of a sudden) dropped in performance. When a user logs in, they are presented with an initial report (Main Menu) which provides links to...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.