cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Reports XI - Oracle 11 - How to improve Performance?

Former Member
0 Kudos

Hello Experts....

Env: Oracle 11 and Crystal Reports XI

I have a requirement where i retrieve all customers with specific open contracts and then depending on type of customer i will go check their activity depending on the contract information calling sub reports.

So my main report is retrieving all open contract customers - which is simple and fine.  than i have two sub reports to call deepening on kind of customer to check the customer activity, this by itself is not too bad in performance it takes a minute but by the time i run for all customer it is taking few hours.

i have changed my subreport data retreival from tables with report selection criteria to command object and passing customer and data range parameters to filter the data  Not sure what else i could do? any suggestions on what i could look for is appreciated.

Thank you,

Accepted Solutions (1)

Accepted Solutions (1)

JWiseman
Active Contributor
0 Kudos

hi Padma,

to add to the suggestions posted here, one of the most important things that you can do is optimize the sql query (or queries in the case of subreports) so that you're bringing in 1 (or close to 1) record for every line of info that you see on the final report. i.e. if you have a simple report with 60 lines of data then the sql query should be as close to 60 records as you can get. if you've got a crosstab with 5 rows and 12 columns you should also strive to bring back 60 records. if you've got subreports, the same type of optimization should be done for each subreport so that the number of records returned from the database is close to the number of records required for all of the subreport objects.

if you use a command object as suggested by Dell that is usually the best option if you want to design the query at the report end as opposed to building a procedure at the database end. it will be pretty much the same speed to use a command. what i would also recommend is that you look into creating a set of subqueries in the main report command object where a subquery replaces a subreport. the command would be grouped by the customer but you'd also bring in one or more subquery results depending on how many different tables you were referencing in the subreports. i.e. one subquery for each different set of tables / result sets that you were getting from the subs.

as Dell mentioned, if you've got more than one line in the main report, you'll have to find out what the grouping level should be in the command as it wouldn't be on customer in that case.

if you haven't built a subquery before in a command object then there's a very simple example here.

cheers,

jamie

Answers (4)

Answers (4)

Former Member
0 Kudos

Thank you very much your responses! Appreciate your time and input.

Yes I am using Oracle native ODBC connection.

When I call the sub report I am passing account parameters from the main report to sub report which filter the sub report data in command object.

When I changed the sub report from tables to command object performance has improved much, but still there could some more I could do.

I cannot make these sub reports to be on demand as once this report is run and output is exported and used in PDF format.

Type of customer is a formula not a database field. What is creating a single command in the main report that will pull the data you're currently using the subreports for based on the type of customer.

My main report has customer information and sub reports have their order activity information.

Sub report is run for one customer at a time as customer account number and date range are passed as parameters from main report for their activity.

I have split the customer activity sub reports into two so I could avoid the union query as it is always one or the other.

BTW – Is using command object (sql) better than directly using tables and giving links.

DellSC
Active Contributor
0 Kudos

Commands are frequently better than linking tables because you can do things like complex filtering or including hints that can't be done with linked tables.

One thing to remember about subreports - they WILL slow down your report.  Every time a subreport is run, Crystal will open a connection to the database and run the query in the subreport.  The connection and the query results are not "shared" between the various instances of a subreport within a main report.  If you are running one or more subreports for every record in your main report, it will slow down your report significantly.

In the main report, does each customer have only one record?  If that's the case, think about how you would write a query that would give you ALL of the data that's displayed in the report (including the subreports.)  Each row will contain all of the customer data as well as the order details that are in the subreports - you would have to formulate customer type in SQL instead of as a Crystal formula.  You then group by the customer, show the customer data in the group header, and the order details in the details section.  This is what I meant by "creating a single command in the main report that will pull the data you're currently using the subreports for based on the type of customer."  If you can get it all into a single query, this will probably be much faster than trying to use subreports.

-Dell

Former Member
0 Kudos

Thank you very much Dell!

I appreciate the explanation.

The main report customer information is not one record.

DellSC
Active Contributor
0 Kudos

So, in the main report a single customer has more than one row in the data?  If this is the case, without knowing more about your data (tables, relationships, etc.), I think you may not have any choice but to use subreports.

-Dell

Former Member
0 Kudos

Hi Padma,

How much time does the SQL require to execute and show results for "All" customers at the backend?

May be, the solution lies in tuning at database(DB), middleware drivers, as well as Crystal Reports(CR) end.

1.Some features at DB-end would be to use indexes or Oracle hints. other option is to write the "All" condition in a stored procedure(SP) DML and have a new subreport 'Only' for the "All" condition. Since SP are pre-compiled there will be definitely preformance improvement.

2. As rightly pointed by Dell, the middleware driver can have a huge impact on SQL execution.Please test with native, ODBC (all drivers i.e Microsoft Oracle, Oracle oraHome etc) and also OLEDB.

3. Some features that can be tested from CR-end:

a) How do you 'call' the sub-reports? Do they accept the parameter values from main report (when you refresh the complete report first-time) and run simultaneously with the 'main'.

If that is the case, then you can break the parallel activity and split the sequence of report execution by making them as "On-demand" subreports.

b) Secondly, you can check if another 'filter' criteria can be used with the subreport SQL that can reduce the report-execution time.

Thanks

Prathamesh

abhilash_kumar
Active Contributor
0 Kudos

Hi Padma,

Does the Customer Activity Subreport take less than a minute to bring in the activities of 'all' the customers or just some?

-Abhilash

DellSC
Active Contributor
0 Kudos

What type of connection are you using to connect to the database?  If it's an ODBC or OLEDB connection, you should look at using Crystal's native Oracle connection instead.

In your subreports, do you return the same type of information?  Or is it completely different?

You could look at creating a single command in the main report that will pull the data you're currently using the subreports for based on the type of customer.  Not knowing how your data looks, it's difficult for me to describe exactly how to do this, but I've done this type of thing using either sub-queries in the From statement or doing Union queries.

-Dell