Poor Crystal Reports Perforamance

AlBrt

Member
Hi,
I installed Crystal Reports and Crystal Enterprise and I use it with JD Edwrards 8.11. And I have problem with very poor performance. A simple report using two tables and downloading about 100 records executes about 2 minutes...
It happens, when I'm using PeopleSoftEnterpriseOne connetction. When I'm using ODBC connection, the execution time is much shorter.

Has somebady an idea, how to improve the performance of Crystal Reports??

Thanks.
AlBrt
 
Evidently the problem is not poor performance by Crystal Enterprise but rather the EnterpriseOne Connection. See Solution ID 201002542 on the Customer Connection website (Oracle/Peoplesoft website).

In a nutshell some brain decided to use XML to pass data from E1 to Crystal - not the fastest way to retrieve data for large-scale reporting.

If you search this site you'll find a post by someone who claims to have found a fix or work-around - but he never posted what the "fix" was.

Good Luck,
 
For JDE World, the Crystal Enterprise is bundled with an ODBC driver from an external party. They also have an equivalend ODBC driver for EnterpriseOne, however this is not bundled with Crystal Enterprise for EnterpriseOne.

You can get more information from:
http://www.unitysolutions.com/index.html
 
You just posted THE topic that I am curious about myself. My experience has been the same as yours. I created a report (using the OBDC driver - JDE integration which inherits security and columns formatted in JDE format) which joins 4 tables. When I process it using the ODBC driver, the report takes 3+ hours to process. I re-created the same 4 table report using a SQL Server driver, and the report takes 4 seconds to process (no lie!). By using the SQL Server driver, I was forced to re-format all date and amount fields, and I lost the "description" fields.

My question is the same as yours, because if I do not find a way to improve performance, I have to find a way to access the "description" fields.

Calling all Crystal Experts...(report) cleanup on aisle 5!
 
Sorry...I was using the PeopleSoft driver (not the ODBC driver) when I was experiencing the "system snooze"...
 
OK - Here we go...

Direct from the Knowledge Jungle:

Summary:
E1: CRYSTAL: Data table linking should not be used with Enterprise One
Details: SOLUTION ID: 200981919 E1: CRYSTAL: Data table linking should not be used with Enterprise One

ISSUE:
Using data table linking in Crystal when using Enterprise One data does not work correctly.

SOLUTION:
While it is possible to join tables from within Crystal Reports using table linking, this implementation should be avoided. Reports will experience severe performance degradation if Crystal table linking is used. Crystal will first retrieve records from the master table. Then for each of these records, Crystal will retrieve the record from the joined table. In addition to the slow response time in the report, the XMLDispatch kernel may stop responding if a large number of joined records are retrieved. In future releases, we will replace client-side linking with server-side linking, which correct the performance and XMLDispatch kernel issues.

WORKAROUND:
Table joins should be implemented using EnterpriseOne business views instead of using data table linking. Another alternative is to use a native database view that joins multiple tables. After you have created the native database view, you can expose it as an EnterpriseOne table, which you can use in Crystal Reports.

SPECIFIC TO: Enterprise One and Crystal

KEYWORDS:
CRYSTAL-CR-APPLICATION
Crystal
Data Linking
Table Linking

For my money, the "workaround" is to create a business view which joins the desired tables in E1, and promote to PRD. Then create the report based on the business view. I will test this on the 4 table join that I described when I get a chance, then report back on my findings.
 
[ QUOTE ]
You just posted THE topic that I am curious about myself. My experience has been the same as yours. I created a report (using the OBDC driver - JDE integration which inherits security and columns formatted in JDE format) which joins 4 tables. When I process it using the ODBC driver, the report takes 3+ hours to process. I re-created the same 4 table report using a SQL Server driver, and the report takes 4 seconds to process (no lie!). By using the SQL Server driver, I was forced to re-format all date and amount fields, and I lost the "description" fields.

My question is the same as yours, because if I do not find a way to improve performance, I have to find a way to access the "description" fields.

Calling all Crystal Experts...(report) cleanup on aisle 5!

[/ QUOTE ]


Here is the solution to your problem.



Table Joins

Often, reports need data from several tables. For example, a report displaying an item number from a transaction table may also need to display the item description, which is found in the item master table. This requires a table join.

While it is possible to join tables from within Crystal Reports using table linking, this implementation should be avoided. Reports will experience severe performance degradation if Crystal table linking is used. Crystal will first retrieve records from the master table. Then for each of these records, Crystal will retrieve the record from the joined table.


Recommendation: Table joins should be implemented using (1) business views within enterpriseone or (2) Use a native database view that you can create and then created the it as an EnterpriseOne table, which you can use in Crystal Reports.

Other settings to change on desk tops with crystal that will give you the desired rest are:

(1)XML Fetch Size

The XML Fetch Size setting is located in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Crystal Decisions\10.0\Crystal Solutions for PeopleSoft EnterpriseOne\QRY.

This determines the maximum number of records that can be retrieved from EnterpriseOne in a single chunk or "page."

The Crystal Reports for PeopleSoft – Integration and Crystal Enterprise for PeopleSoft – Integration installs set the value for the setting to 300. You could set this to 2000




(2) XML Request Timeout

The XML Request Timeout setting is located in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Crystal Decisions\10.0\Crystal Solutions for PeopleSoft EnterpriseOne\QRY.

The XML Request Timeout setting determines the number of seconds until a single request times out. This setting should be set to a large number to ensure that reports complete.

The Crystal Reports for PeopleSoft – Integration and Crystal Enterprise for PeopleSoft – Integration installs set the value for the setting to 180000. You could set this to 250000

Use the REGEDIT command to do this our you could contact me if you do not know how to do this.
 
Using E1 Custom Business Views in Crystal reports

We use E1 Custom Business Views for crystal reports and If the selected column in data selection is not part of the Key (E1 custom Business view) the report is not responding and we have to kill task in Windows Task manager to get out.

We are on E1 8.11 (AS/400 I series) Crystal Report version 10.0

Please advise.
 
Back
Top