Subreport performance issues

[gfarrant]

Hi everyone,

I have a performance issue with multiple subreports that I would like to understand.

First the basic specs:
* CR version: 7.0
* Report connects to a MS Access database via ODBC
* Using the CR API to launch the report
* Using C++
* Viewing reports via ASP (but viewing them on the same server that generated the report)
* Machine is a Dual PII 400 with 512Meg RAM

The situation:
We are generating a report with 4000 records. For each record there are 2 subreports.
One subreport contains up to 4 fields selected from a table which is indexed on the field used to join with the main report.
The other subreport has up to 16 fields (but usually only one or two) and it is also indexed on the join field.

It is taking approximately 40 minutes to run the PeStartPrintJob API function. Once the report is generated it is taking about 10 minutes to view the first page of data in Crystal Reports Designer and much longer through ASP and the viewer. It then takes approximately 6 seconds to go to each new page as the subreport data is populated.

We are using subreports for two reasons. In one case the users have the choice of which fields they wish to view at run-time (4 fields max). In the other case we do not know at run-time how many fields are required (1-16 fields).

The question:
I understand that generating the report will take a long time because of all the extra queries (8000 in this case). What I don't understand is why it takes so long to generate the report AND also takes so long to view the report. I would have thought that after the report is generated all of the hard work has been done.

If anyone can tell me what is actually happening that would be great. If anyone can suggest ways to improve the situation, especially with the delays in viewing, that would be even better!

Thanks in advance.

Glenn

 

[IdoMillet]

If you describe the situation in more detail,
it may be possible to suggest alternative solutions,
but assuming we are stuck with the subreports
approach:

1. Make sure you are not running on Windows NT,
Service Pack 3.

2. You should be able to eliminate the delays
in viewing (at the expense of longer initial load time)
by adding something like a 'Total Page Count'
special field to the report, forcing Crystal to
format the whole report immediately rather than
upon navigation to each page.

Cheers,
- Ido ixm7@psu.edu

 

[gfarrant]

Thanks Ido,

I will try the 'Total Page Count' field.

Basically we are stuck with the subreports if we are going to use Crystal to merge the data. Linked tables will unfortunately not do what we need.
The next step may be to do some more processing at the database end so Crystal doesn't have to do so much work.

Thanks again

Glenn

 

[IdoMillet]

Glenn,

Shifting the work to the DBMS is the correct long-term solution. If you need help with the SQL, please provide a more detailed description of the situation, including tables, DBMS type, etc.

Cheers,
- Ido ixm7@psu.edu

 

[gfarrant]

Ido,

Thanks for confirming the correct route and the generous offer of assistance. I will have a go at it and get back to you if I need a hand.

Cheers

Glenn

 

[dgillz]

I also had some performance issue with subreports and I solved some of them by using "perform grouping on server". This only applies if the main report or subreports show summary data only - if you need to see the detail this will not help you at all.

What this basically does is crunches all the numbers at the server, and sends only the summary data to the workstation, thus cutting down on a lot of network traffic.

Try it, see what you think.

 

[Ngolem]

"We are using subreports for two reasons. In one case the users have the choice of which fields they wish to view at run-time (4 fields max). In the other case we do not know at run-time how many fields are required (1-16 fields)."

ARE YOU SURE subreports are necessary? You are making a lot of hits to data bases for simply letting a user see a certain set of fields. I think Crystal's suggested maximum no of hits to a subreport is something like 70 and you are working on 8000.

Are the tables in the subreports able to join onto main report?

It would seem to me that designing the report such that it draws over all the data and then using the user inputted parameters to selectively show specific header/detail/footer sections would be much faster than what you are currently using.

4000 records is not a lot of records for Crystal to handle. I have many reports that handle 5-10x that number of records that must be processed a second time through the Record Section formula that only take 5 mins max to run .... and this is an extreme as I usually target for 1 minute or less.

I agree with IDOMILLET....you should show us more details to find a better design to the report.