Speeding up report with two subreports

[shaunhubbs]

Hi all,

I have a main report that has the items "picked" for a surgery (called a "pick list") and it contains two subreports:
(1) an "explosion" list that contains those items that are in a set specified on the "pick list"
(2) a "worksheet" that has the same items picked on the "pick list" only sorted differently for a different set of people and also includes "case notes" on how a surgery is performed

Each report requires two parameters:
(1) hospital
(2) date

I have linked the reports based on:
(1) hospital parameter
(2) date parameter
(3) a unique booking identifier given to each booked case
(4) the surgeon
(5) the procedure

The linking is done because the two subreports are in the group footer so that the final printed report will collate the pages so that is in order pick list, explosion (if applicable), worksheet for each surgeon/procedure combination (as each surgeon has their own preferences even if it is for the same surgery and even if two surgeons are working on the same patient for the same surgery).

Each of the reports/subreports is based on its own stored procedure as they contain slightly different information. The pick list and worksheet, however, only differ in the worksheet has the case notes with it. The case notes are a memo field and are quite large so I figured that I would leave them out of the stored procedure for the pick list.

Any ideas on how to speed up this report or does it sound like it is set up the way it should be? Currently, for our biggest hospital, the report takes about 15 minutes to spew out just under 500 pages for the day. Does this sound reasonable?

Maybe I expect too much and it is already set up the way it should be, but maybe one of you fine folks has a suggestion.

TIA.

- Shaun

 

[shaunhubbs]

One quick note to add:

It seems to me that in my current configuration the pick list report is run through to the footer where the subreports are and then it runs the entire "explosion" report and picks only the information that corresponds to the surgery in the pick list above and then it runs the entire "worksheet" report and, again, only picks the corresponding information (and that one is very slow with the case notes attached). Is that how it is supposed to be run? Again, a better way to do it?

- Shaun

 

[Madawc]

If the subreports use separate stored procedures for two different sets of data, it ought to be possible to tinker with it and get a single stored procedure that collects all of the data for the entire report. This will be much quicker than separately collecting data for each group footer. (But that assumes you are able to modify the SQL, and can take the time to learn what it does.)

A possible alternative - depending on your data structure - would be to add the data to the main report, maybe using left-outer links.

Be sure to take a back-up of the existing report and its stored procedures, which at least work, however imperfectly.

It helps to give your Crystal version, since newer versions have extra options, and some extra problems.

Madawc Williams (East Anglia, UK)

 

[shaunhubbs]

Sorry. I am running CR9.

Also, I do have full working knowledge of the SQL as I created the statements. But as I mentioned previously, I slightly modified the stored procedure between the "pick list" and the "worksheet" because I noticed that when I was running the pick list off of the same procedure it ran a lot slower as it was loading those case notes. I fail to see how them all running on the same stored procedure would help rather than hinder. If you (or someone) could maybe clarify that for me that would be great. As I understand it, I will actually have 100 nested versions of the worksheet report if there are 100 pick lists, but only the data for the corresponding one shows up. Is this correct? This is what I am attempting to find a way around...

Also, were you referencing left-outer links within the report? Between the stored procedures within the report? Or were you speaking about a modification to the SQL stored procedures?

Thanks again!

- Shaun

 

[Madawc]

Left-outer links are an option within Crystal. You might be able to collect all of the data in the main report. And this would speed things, because the 'Server' would do it just once, rather than the machine running the Crystal having to colect more data for each group.

Madawc Williams (East Anglia, UK)

 

[shaunhubbs]

Understood. Thanks again. But I think that the reason it may not work in my case is because I have repeating data. The worksheet and the picklist are the same thing just sorted differently if you take out the case notes attached to the worksheet (which is what is causing the slowness in the first place). Any suggestion on how I could get the same data to repeat twice like that in the main report without using a subreport?

- Shaun