Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

Improving Performance of Crystal Reports

My application was created using VB and Oracle and it uses Crystal Reports 10 as the reporting tool. I was wondering if there is any way to improve the speed at which crystal reports loads. I noticed it takes VB less than a second to create a recordset of 300,000 records but it takes crystal reports ages to display the result. Thanks
0
oagunbiade
Asked:
oagunbiade
1 Solution
 
flavoCommented:
that's a lot of formating for CR to do...  Vb has it easy!
0
 
AnnetteHarperCommented:
Check the Report | Performance information, particularly the Performance Timing and Processing entries. Look at the difference between the query execution time and the record reading time. That will tell you how much time is being used to create the record set versus fetching the records. Look at how many passes the report has to make. One is best, two is normal, three should be avoided. The other entries will also help you determine if you can change the report to decrease processing time. You may be able to push some processing back to the server, etc.
0
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Have a look at: http://www.tek-tips.com/faqs.cfm?fid=489

Cheers,
- Ido
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
oagunbiadeAuthor Commented:
Thanks for the replies. But Annette the recordsets for my reports are created at run time by code and passed to the report. Will i be able to check the report / performance information for that?
0
 
frodomanCommented:
The link Ido posted has good tips & general information.  If you tell us more about your report we may be able to help you apply that information to your situation.

frodoman
0
 
AnnetteHarperCommented:
I'm not sure what you'll see in that case for the query time, but all the entries that concern the Crystal formatting side will still apply.

Are all 300,000 records displayed in the report? Or is there some record filtering? Is this report meant to be viewed interactively or printed for reference?
0
 
oagunbiadeAuthor Commented:
The actual database holds about 600,000 records and the 300,000 records are results from an sql query. The user is given the option of printing the report or viewing it interactively. I know 300,000 is a lot but im using this as a test for the worst case senario.
0
 
AnnetteHarperCommented:
It's going to take a significant amount of time to format 300,000 records for display no matter what you do, but you may be able to make some improvements in the report processing time.

But first, have you considered creating a summary report with drill-down? User aren't generally going to look in detail at 300,000 records and are probably concerned with only the totals or subtotals. Using a summary report, but allowing drill-down gives you much faster processing but still allows the users to see the detail if they need it. This technique will only give you much improved times if the grouping is passed to the server. In that case, the original report query returns only the summary records, then if a user drills down, a second query is sent to fetch the detail records for the group that was drilled into. The downside is that drilled down sections can't be printed with the main report.

Otherwise, are you using any formulas in the report? How complex are they? Can that logic be pushed back to the server? Are you using only read time formulas or also print time formulas? Print time formulas require a second pass of the data. Are you using the total page count special field? Don't! It requires a third pass to process.

What database are you using? What connectivity driver are you using?

There are many, many different aspects of report performance.
0
 
oagunbiadeAuthor Commented:
I did not use drill downs but ive given the user the option of view a detailed or summary report which are not linked in any way. I am using about 2 formulas in the report which are not very complex just simple additions ( i would look into pushing this to the server ). I am using only read time formulas and im using the total page count special field (i will take this off). Im using a Oracle 9i database and using the Oracle Provider for OLE DB 9.2.0.1.0 client driver to connect to it. My main concern is the software is designed to work in a wide area network and im trying to avoid any performance delays.
Thanks
 
0
 
oagunbiadeAuthor Commented:
Oh one more thing the report also contains 2 subreports
0
 
AnnetteHarperCommented:
Where are the subreports? Each instance of a subreport (each time the subreport prints, not the definition of the subreport) requires a separate database query. You want to limit this as much as possible by placing subreports in the report header or footer or high level group headers or footers and avoid placing them in the Details section. In your case, if you had a subreport in your Details section, an extra 300,000 queries would be needed to process the report.

If your formulas are simple, you probably won't gain much by pushing them to the server, but you can always make the comparison.
0
 
oagunbiadeAuthor Commented:
Thanks Very much Annette u have been very helpful. One more question before i close this. Is there a way to get CR to display the first page of the report while the remaining are still procesed?
0
 
AnnetteHarperCommented:
Crystal will read all of the records before displaying the first page, but it will not completely format any pages that have not yet been viewed. I don't think there is anything you can do to change this. So setting up Oracle to return the first records quickly will have no impact.

You might consider displaying some portion of the records using a parameter and then have a link on the last page to execute the report again for the next portion of records. (This is off the top of my head, don't know if it would work, and you'd lose your totals, of course.)

If you are interested in more optimization techniques for Crystal on Oracle, you can look at my book, "Crystal Reports 9 on Oracle."
0
 
oagunbiadeAuthor Commented:
Thanks once again
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now