Report is generating very slowly

[Former Member]

Hi all, my crystal report is generating very very slowly my report is having many sub reports inside it as well,

see the time difference in the following lines that comes out while generating the report from my Application, please comment how can i solve this

03/26/2014 06:03:47 PM ... Process({User I/F} Priority: 4, user interface)
a RDCReport is getting OLE property "SQLQueryString" with arguments: ()

03/26/2014 06:07:38 PM ... Process({User I/F} Priority: 4, user interface)
Get OLE Property "SQLQueryString" returned: SELECT "TABLE"."COLUMNS" .................... long long query here

No functions or cursors have applied in crystal report but still it is taking too long to generate

[JWiseman · Accepted solution]

hi Muhammad,

what happens if you run the report in the report designer, i.e. crw32?

i would suggest looking at your subreports as running a lot of details level subs in a report is going to be slow. if you've got subs on the details level, one query will be run per subreport on each record, which will eat up a lot of processing.

sometimes subreports can be eliminated as well...for example, if your subreport contains the same tables as your main report, you can get rid of a subreport and substitute it for a cross-tab if you're only showing data in tabular format.

try suppressing all of your subreports as well, and then un-suppress them one at a time, refreshing the report each time a subreport is unsuppressed. perhaps there is one particular subreport that is a problem.

if you only find that your subreport is slow in your application, please post a new discussion to the crystal reports for visual studio forum.

-jamie

[Former Member · Accepted solution]

Cheers Jamie for the quick reply,

I had now suppress all the sub reports from the main report but still it is taking the same amount of time i,e, 7 minutes to generate the report and the main time consumption is showing at the same point i,e,

03/26/2014 06:03:47 PM ... Process({User I/F} Priority: 4, user interface)
a RDCReport is getting OLE property "SQLQueryString" with arguments: ()

03/26/2014 06:07:38 PM ... Process({User I/F} Priority: 4, user interface)
Get OLE Property "SQLQueryString" returned: SELECT "TABLE"."COLUMNS" .................... long long query here

[DellSC · Accepted solution]

This looks like it might be the main query for the report.  Did you link tables together in your report or are you using a Command or a Stored Procedure?  What type of database are you connecting to?  Also, please post the formula that's in the Select Expert.

-Dell

[Former Member · Accepted solution]

Thanks Dell for the help here are all the answers you asked 

This looks like it might be the main query for the report. 

Correct!! As i have suppress my all sub reports but it didn't help at all....

Did you link tables together in your report.. or are you using a Command or a Stored Procedure?

Yes I have one table from which i am giving the link to other 7 tables and those 7 tables are also linked somehow with each other

What type of database are you connecting to?  Also, please post the formula that's in the Select Expert.

I am connecting to Orace 11g and here is the one line formula in my main report

{Employee.ID} = "00029/2014"

[DellSC · Accepted solution]

Did you link tables together in your report.. or are you using a Command or a Stored Procedure?  
Yes I have one table from which i am giving the link to other 7 tables and those 7 tables are also linked somehow with each other

This could very well be the issue.  How are your SQL skills?  You might get better performance if you get your data using a Command, which is nothing more than a SQL Select statement.  If you decide to go that route, you'll need to put the filter in the Where clause of the command instead of in the Select Expert where it is now.  If you don't, Crystal won't push the filter down to the database and instead will pull all of the data into memory and filter it there.

Before going there, though, can you provide some information about how the tables are linked together - the table and field name for each link that you have?  I'll take a look at it and see if there's a way to simplify it.

Thanks!

-Dell

[former_member183750 · Accepted solution]

One other thing to consider - and I am hanging my comment on this:

RDCReport is getting OLE property

Assuming that this is an app that uses the Report Designer Component (RDC) SDK;

Depending on the version of the RDC, Oracle 11g may not be supported. I'd still follow Dell's advice, but keep that in mind. BTW; this is an RDC app? What version of CR?

- Ludek

[Former Member · Accepted solution]

Yes Ludek

This  application is using the Report Designer Component (RDC);

The version of the RDC is 11,

The version of CR i am using is 11 as well

[DellSC · Accepted solution]

What language is your app written in?  If it's a .NET app, which version of Visual Studio are you using?

-Dell

[former_member183750 · Accepted solution]

To add to Dell's query:

CR XI is not quite precise enough as it could mean CR 11.0.x, or CR 11.5.x. And that is a big, big difference. In any case I am pretty sure that neither CR 11.0 or CR 11.5 supported Oracle 11g. But. If you are not using CR 11.5, see this blog on how to upgrade to it. Only reason I'd recommend 11.5 is that it is quite a bit newer than CR 11 and it may be able to use Oracle 11g, even if it was not supported officially. You'll have to keep your fingers crossed...

Other than that, you will have to upgrade to CR 2008 (v 12.x) and port your app to use the CR SDK for .NET as the RDC was retired in CR 11.5.

- Ludek

[Former Member · Accepted solution]

I solved the issue by following Dell advise that is to eliminate so many link tables from the main report created sub reports instead

So now my report is taking 35 seconds to generate that is previously taking 7 minutes

CHEERS DELL

[Former Member · Accepted solution]

HI Ludek Uher

As you stated:

Other than that, you will have to upgrade to CR 2008 (v 12.x) and port your app to use the CR SDK for .NET as the RDC was retired in CR 11.5.

Can upgrading to the Crystal report version 2008 will eliminate the time taken by the

a RDCReport is getting OLE property "SQLQueryString" with arguments: ()

?????

Thanks

[former_member183750 · Accepted solution]

Only one way to find out... too many variables for me to tell you yes / no / maybe.

- Ludek