Long Running Crystal Reports 1

[cheskey1977]

Hello All,

We are running into some Crystal Reports that run in the ballpark of 1+ hours. I was wondering if there are problems with Crystal Reports (version 6) handling (read: returning and running calculations on) large amounts of data.

We are using-

DB: Oracle 8i (on a Sun/Unix platform with 16X400 proc, 16384 RAM)
ERP: PeopleSoft 7.5 (App/Server on same UNIX machine)
Reporting: Crystal 6, and some queries may return upwards of 80,000 rows to use in calculations
Client OS: Windows 95 (I know, I know...)


In testing we are opening the Crystal reports on the Client Window, and they can take up to an hour or hang.

Any advice or personal experiences with long running reports would be greatly appreciated.

Cheers.

 

[synapsevampire]

There are many factors which can contribute to long running reports, the most common is poorly constructed Record Selection Formulas.

Since you haven't shared any specifics about the reports themselves, I can't address that.

Here's some basics to help:

Use Stored Procedures for long running queries.

If you're basing reports on tables, make sure that the SQL passed to the database reflects the record selection criteria, and only bring back the rows required. 1.5 hours is unreasonable for 80,000 rows, so you're probably fitering rows within the report, rather than on the database, hence the lag.

Use Database->Show SQL Query to determine what is getting passed to the database for processing. The Where clause should reflect the criteria in the Record Selection Formula.

Use Report->Edit Record Selection->Formula to determine the fitering criteria being used

If you post the record selection formula and the SQL being generated, someone here can help you to optimize, or better yet, rather than fitering from within Crystal, do so on the database server by using Stored Procedures and parameters.

-k

 

[DataDog]

cheskey1977 : synapsevampire helped me on one quite like this less than a week ago.

Our reports now run 25 TIMES Faster!

We have date fields in PassPort (which feeds PeopleSoft AP) that are Strings (i.e."20030302" for March 2nd) and a Parameter that is Date Formatted (i.e. 03/02/2002). After we followed synapsevampire's suggestion to do a Select Expert FORMULA for converting the Parameter Date to Database Stringwe were home free.

See my initial post:
thread149-483024 DataDog
'Failure Is Not An Option'

 

[chelseatech]

A few years ago I had some CR6 reports that I made a few changes to when CR7 came out and they went from 1hr 15 mins to under two minutes. that requried using some SQL expressions to speed up record selection.

I had a client running the earlier version 7 of my report scheduler. Installing the upgrade and the CR8.5 run time with no other changes made them run 20x times faster.

If you want faster reports, CR6 is not the tool to use.



Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm