How to improve performance in Crystal Reports

[Former Member]

Hi,

I am working on a report which performs pretty poorly. It's a report created with Crystal Reports for Visual Studio 2008. I need to generate a report for every person in my database. The report works just fine, the only problem is the time it takes to generate. The report is pretty complex, it consists of one mainreport and there are 6 subreports on this report. So I think my best chance on improving the performance is to get rid of these (if possible) subreports.

My question is how to handle this? I need to format my report in the same way, but (if possible) without the use of the subreports. Do you have any ideas how to achieve this? So how can I reduce the number of subreports and keep the same formatting in my report?

Hope you can help me out on this one.

Thankx,

Martijn

[former_member183750 · Accepted solution]

Hi Martijn

It's difficult to say if removing subreport will help, or how to structure the report without subreports as I don't know what the report is supposed to do. Nevertheless, here are a few resources for your consideration:

1) As you are using Crystal Reports Basic for Visual Studio 2008 (v. 10.5.x), make sure you are updated with Service Pack 1:

2) You don't mention the database you are using or how you are connecting to it. CR 10.5 being from 2008, will not support MS SQL 2010, or 2012. Similarly, all other new databases will more than likely also not be supported. E.g.; Oracle 11g, etc., etc.

3) Have a look at the doc Crystal Reports for Visual Studio .NET Scaling Crystal Reports for Visual Studio .NET

Also see:

http://jamieodonoghue.net/2012/09/crystal-reports-performance-overview/

KBA: 1636361 - How do I design my Crystal Reports to improve performance?

Finally, seeing how the report behaves in a later versio of CR may be worth a try. You can download CR 2008 (this is the last version of CR to support VS 2008) from here:

SAP - Crystal Reports 30-Day Evaluation

And a tip, in case you are using MS SQL:

For OLE DB use:

MS SQL 2005 - OLE DB Provider

MS SQL 2008 - SQL Native 10

MS SQL 2012 - SQL Native 11

MS SQL 2013 - SQL Native 11

For ODBC use:

MS SQL 2005 - SQL Native

MS SQL 2008 - SQL Native 10

MS SQL 2012 - SQL Native 11

MS SQL 2013 - SQL Native 11

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[Former Member · Accepted solution]

Hi Ludek,

thanks for your reply, I will take a good look at the resources / links provided by you. Just to give you some more information; we use an Oracle 11G database in which we have created a view especially for this report. This view contains all data we need to report on. From this view we create a dataset, this dataset in linked in the report. By the way, it's a desktop product that we have created, no web or online reports are needed.

I see a major improvement when I don't use subreports, but it's hard to create the same report without making use of subreports.  

What I am actually looking for is a (step-by-step) document of some kind with best practices on how to reduce the number of subreports, in other words how to rebuild my report (so it contains the same data and looks - pretty much - the same) so it performs better.

Any more tips are greatly appreciated.

Thanks,

Martijn

[DellSC · Accepted solution]

Martijn,

A lot of what you're asking for will be very specific to your report and its requirements and layout.  If you provide us more information about how you're using the subreports, we may be able to point you in the right direction.

Having said that, here are some general rules for working with subreports:

1.  Try to not use them in Details sections.  Every time a subreport is called, it will run a separate query on the database - so, for example, if you have a report that has hundreds of records, it will run hundreds of queries on the database - one for each detail record.  Subreports are best used in Group or Report header or footer sections.

2.  Use the subreport links to filter the subreport data down to as few records as possible.

3.  Avoid Crystal formulas (other than "IsNull()") and formulas you've written in the subreport in the selection criteria - these will cause Crystal to pull all of the data into memory and filter it there. (This is also true for filtering your main report.)

4.  In both main reports and subreports, I will frequently write a command (SQL Select Statement) to pull the data instead of linking tables because I can usually write more efficient SQL than Crystal will.  I've also found that I can frequently do things like pivoting or aggregating data in the query which pushes the processing to the database where it's more efficiently done than pulling many more rows into Crystal and letting it process the data.

-Dell

[Former Member · Accepted solution]

Thanks Dell, I will try to explain my report some more. I attached threeimages to my reply. One image shows what dataset is used in the report, and how the the layout of the report looks like (combined image). On this image the subreports are shown in light blue color (just to make things clear to you). I have colored the subreports to show you in which section they are used.

The other images are page 1 and page 2 of the output of the report.

It's a report which is run for one person of for all persons in a specified project, in some situtations the report will be generated for up to 2000 persons, the resulting report will be over 6000 pages in size. One of the options is to export the report for each person to a separate pdf. Every second of performance improvement has a major impact when looking at these numbers!

The dataset is populated by an Oracle view and in the report there is some record selection done to determine which field is shown in which subreport. In this dataset a lot of data is duplicated and I think this might be one of the issues to address.

I am planning to normalize the dataset so data in there is related by keys, I think this will make the subreports run faster because there will be less data in the tables to look for. Do you agree with me on this?

When reading through documentation of Crystal Reports I often read that it is important to use indexed fields. Right now I can not place an index on my dataset because there are duplicate values in it, and there is a unique constraint for the index on the dataset. When I normalize my dataset, I think I will be able to put an index on the dataset. Or am I misinterpreting the documentation and is the indexed field only usefull when placed directly on the database table?

I hope this helps understanding my issues and requirements.

[DellSC · Accepted solution]

After just a quick look at this I have a couple of comments:

- If the address data is in your single data set, you don't need a subreport for it.  You should be able to get it to format correctly within the main report.

- I would split up the data into multiple tables as follows:

     1. Just the data required for the main report - name, address, etc.

     2. One table for each subreport with just the data for that subreport plus a field to link to the main report so that it can be filtered appropriately.

     3.  The unique constraint in a dataset only applies to a "primary" key - you should be able to create non-primary index.

-Dell

[Former Member · Accepted solution]

Hi,

I am working on re-modelling the data right now. But this also means I have to redesign my report (because the tables will be different), so i'ts quite some work.

I will keep you posted of my progress and hopefully I can present a good result. I will share my findings for future reference.

Thanks

[Former Member · Accepted solution]

Hi,

I finished remodelling my dataset and I have redesigned my report and I have managed to achieve a major improvement in performance. I think reducing the number of subreports and limiting the data in the datasets for the subreports is the main improvement.

One thing I still need to figure out is how to show progress to the end user. In my C# code I have a line of code; reportviewer.refreshReport()

I need to call this function in order for the report to refresh and show. But depending on the size of the report this can take several minutes. At the moment I am showing a animated GIF to  'fake' progress. What I would like is to show the actual progress, how many pages of total pages are already loaded?

Is there a way to show actual progress? Like for instance; "Loading page 24 / 45".

Any suggestions are greatly appreciated!

Thanks!