Crystal Viewer takes a long time to load report

[SBpsc]

Hi:

Can anyone explain to me how the 'Save Data with Report' really works. We are using Crystal XI and some complex MySQL queries to create these reports. The reports are being generated and distributed via the web. However, whenever there is a large amount of data, the viewer takes minutes to load the page. I can understand it taking minutes to create the report but once the data is saved, why does it take so long to display it? Any references or pointers is greatly appreciated.

Thanks,
SB

 

[hilfy]

If there's a lot of data that's been generated for the report, it all has to load to the workstation in order for the report to display. When data is saved with a report, the actual dataset is saved and then the report is rendered based on the data. In other words, the report is not saved as if it were a .pdf or other type of static document - the data is actually "read" from the saved data and the report, including all formulas, is generated from that data.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[smcnulty2000]

The report file consists of two things; the report definition and the saved data. The definition part is pretty small. The saved data is compressed on the disk for greater disk usage and can be quite large.

When you resummon a report with a web interface, your display has to go through the decompressing and redisplay of the data. It also goes through the whole reconstruction of the pages, page numbers, etc.

It can take as long as requerying the database in question. Mostly, it would seem best if the report would all be in memory at the same time, but I don't think Crystal gets this. I also believe that since you are going through some Java to get the display filled, you can see a slowdown there.

So your bottleneck factors are: the hard disk, the java vm, the size of the saved file, whether it is internally indexed (which slows down some files and speeds up others).

I recently had a report that was 93K without any saved data, and 99M with saved data. My machine thrashed all over the place trying to do a save of any kind. When I turned off the 'save data with report' I was suddenly able to do what I wanted to the report quickly even with a preview page open. On the same server I've set certain big reports to get distributed in Excel and/or PDF format on a schedule. This works fine because we're not going through a display.

I don't have any reference beyond the help file. You might try the subject "report bursting indexes" in a help file search. This might shed a little more light on some options.

Perhaps someone else here has a little more info.

Scott.

 

[SBpsc]

Thanks Guys...Your information was very helpful to me. I am still pretty new to Crystal Reports so your details are helping me tremendously.
On that note, I do have another similar question that maybe you can help me out with. I have a report that has no parameters in it. When I view it on the web, it takes about 3 secs to load. If I modify the report and add a date range parameter (with the dates hardcoded into Crystal Reports), it still takes about 3 secs to view the report. However, if I put in a date range and have the customer enter the date (or pass it programatically), it takes about 20 secs before I can view the page? Can anyone explain this? Does Crystal load all the data anyway and then sort by date range?

Thanks.

 

[Turkbear]

Hi,
In the CR designer open the report and look at the 'Show Sql Query' option under the Database tab..

See if the date ranges are being passed ( that is, are they in the Where clause?).

If not, then Crystal is returning ALL the records and doing the date range selection after that..Can be very slow.

How are you having the dates specified..As Date parameters?,
Strings converted to Date(String) , etc..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SBpsc]

The whole report is based on a MySQL query using the Add Command option. However, the date passed by the query is coming in as a String so I have to convert it to a Date in Crystal Reports. The parameters are then set in Crystal Reports and passed to the viewer. Its odd how passing parameters is slowing it down however, the same report without the parameters is running very fast. They are the same file size and have the same number of records in it.

Thanks.

 

[Turkbear]

Hi,
Did you define the parameters when creating the Command Object?( not the usual CR parameters)
..These are then passed to the query before the results are returned..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SBpsc]

The query has another parameter but not for the date range. The parameters are set using CR parameters. I tried passing the parameters directly to the MySQL query but then when I tried to run the report, I kept getting the "SQLBindParameter not used for all parameters". However, if I place the same query (with the parameters in MySQL) into a new blank report, they work just fine. I am not sure why its not letting me update an already existing report with additional parameters. (The MySQL query looks something like the following...

select ...
from ...
where tb1.id = '{?id}' and tb2.date > {?start} and tb2.date < {?end}

Thanks.