Crystal Reports 2013 performance issue

[Former Member]

Hi experts,

We're facing a performance issue on a report designed with CR 2013 edition.

We've a heavy report with many formulas(near 30) and some queries as source (6).

The report deals with many data (up to 10'000'000)

But my question is about ressources utilization.

We refresh the report with client version and the "reading data" part takes many time.

With less data (2'500'000 lines scanned), timing metrics are as follow :

- Query : 1'537 ms

- Read Data : 1'268'070 ms

- First page layout : 1'269'712

But in task manager, I don't see a big usage by CR.

Is it possible to configure CR to use more ressources ? to parrallelize some tasks ?

refreshing the report through desktop version, is there anything to check on server side or is it irrelevant ?

Or do you know which elements can have an impact on "read data" part ? I guess it's the time spent to calculate the formulas but i'm not sure of that.

Thank you in advance for your answers.

Regards,

Thomas.

[abhilash_kumar · Accepted solution]

Hi Thomas,

There are numerous reasons for a report slowing down. It is very difficult to zero-in on the cause without looking at the report itself. Also, 10'000'000 is a lot of data!

Here are a few points that you'd want to check though:

1) Does the report Join a Command SQL to another table or another Command SQL?

2) Does the report use Date conversion functions in the Selection formula?

3) Does the entire selection formula translate to the 'where' clause if the report has been solely built using tables/views?

4) If the report's datasource is a Command SQL and has a prompt, has the prompt been used in the where clause and not the selection formula? (it should be in the where clause of the query)

5) Is there a subreport that's slowing down the report?

6) What section is the subreport on? Subreport's placed on the details/ group sections further slow down the report as they run for each record.

6) Have you used the 'Page N of M' special field?

and a few more....

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash

Here are your answers, by the way I know that 10'000'000 lines is very huge :

- 80% are command and not tables. And they are linked with outer join in most cases.

- No date conversions used

- N/A

- No prompt used

- No subreport

- No use of N/M pages.

I was wondering if a config file exists.

I found one whom seemed to be linked to for CR4E but not for CR2013.

Regards,

Thomas.

[abhilash_kumar · Accepted solution]

- 80% are command and not tables. And they are linked with outer join in most cases.

This is where you should start!

You should avoid Joining multiple command objects within CR as the Joins are not processed by the database server. You should try to combine those two command objects together into a single command.

The idea is to pass as much work to the database server as possible and return as many less records as possible back to CR.

-Abhilash

[DellSC · Accepted solution]

For more information about using commands and a description of why you don't want to join them together, see here: 

-Dell