Solved

Improving Performance of Crystal Reports

Posted on 2004-09-16
14
540 Views
Last Modified: 2008-03-06
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
Comment
Question by:oagunbiade
14 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 12073167
that's a lot of formating for CR to do...  Vb has it easy!
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 12073674
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
 
LVL 22

Expert Comment

by:Ido Millet
ID: 12073880
Have a look at: http://www.tek-tips.com/faqs.cfm?fid=489

Cheers,
- Ido
0
 

Author Comment

by:oagunbiade
ID: 12073936
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
 
LVL 42

Expert Comment

by:frodoman
ID: 12074056
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
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 12074071
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
 

Author Comment

by:oagunbiade
ID: 12074629
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Accepted Solution

by:
AnnetteHarper earned 500 total points
ID: 12074763
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
 

Author Comment

by:oagunbiade
ID: 12075168
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
 

Author Comment

by:oagunbiade
ID: 12075281
Oh one more thing the report also contains 2 subreports
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 12075374
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
 

Author Comment

by:oagunbiade
ID: 12075802
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
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 12075917
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
 

Author Comment

by:oagunbiade
ID: 12076011
Thanks once again
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now