Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I improve report processing performance?

Report Development

How do I improve report processing performance?

by  emcevoy  Posted    (Edited  )
You can visit seagate's web site and download thier technical document on tips to improving report processing speed which should help. You can find this white paper through the following links:

http://community.seagatesoftware.com/communityCS/TechnicalPapers/repcomp.exe
http://community.seagatesoftware.com/communityCS/TechnicalPapers/SCR_RecSel.PDF
http://community.seagatesoftware.com/communityCS/TechnicalPapers/scrtips.pdf
http://community.seagatesoftware.com/communityCS/TechnicalPapers/aliases.exe

Other suggestions are as follows

Report Dependent Issues

a.)If you have a large database (>10,000 records), try to use a selection formula to only return those records that you need for the report, rather than having Crystal read all your records.
b.) If you ARE using a selection formula, make sure that you are NOT referencing any Crystal functions (ie. ToText, DTStoDate) or formulas. If it is referencing Crystal functions or report formulas, your database will return ALL RECORDS because it does not understand the Crystal functions on the database side. Instead, use your Crystal functions in specific formulas rather than the selection formula.

c.) If you are using Subreports, the fewer subreports you have, the faster the report will run. Each subreport will hit the database AGAIN to gather data. Also, LINKED subreports will always perform faster than unlinked subreports, because they are being run on a subset of data being passed from the main report, rather than returning all records.

d.)If you are using text objects, and can avoid inserting database or formula fields inside them, your report will run faster.

e.)Make sure you have plenty of free hard drive space available on the drive that your "SET TEMP=<path>" statement in your AUTOEXEC.BAT file is pointing to. Crystal uses that path to store its swap files as it generates your report. Depending on the complexity of your report, you may require anywhere from 10 to 100 megs of free space to generate your reports.

f.)The more OLE objects you have inserted, the slower the report will run. The bigger or more complex the OLE object is, the slower the report will run.

g.)If you do not want to show sections on your report, try and SUPPRESS sections (no drill-down) if you don't need to see drill-down information. This will speed up report performance, as hiding a section which will calculate and save drill-down information takes longer than a section that is suppressed (no drill-down).

h.)Make sure that ôUse Indexes for Speedö is checked ON under File|Report Options, other-wise PC database reports will not use indexes to retrieve information, and SQL database reports will return the entire dataset before Crystal filters it on the local machine.
i.)Creating a stored procedure or parameterized stored procedure, and reporting off that, is by far the quickest way to retrieve a dataset from the server.

Database Dependent Issues
Specific to PC Databases:

a.)If you are accessing PC Databases, always try to use native database drivers to access your data rather than ODBC. ODBC will always perform slower than native access, as information must be "translated" by the ODBC driver before being sent to your database for retrieval, and then again on the way back, the returned information must pass through the ODBC driver to be translated into your report.
Specific to SQL Databases:

a.)With SQL databases, both ODBC and Native drivers that ship with Crystal 5.0 are comparable In performance.
b.)If you modify the Database|Show SQL Query statement in Crystal, any selection criteria you add later to the report will be processed locally on the client side. You must make sure to reset the query first, and then add a selection criteria to the report, which in turn will then be appended to the existing SQL query so that processing occurs on the SERVER side.
c.)Selections on Parameter fields WILL be sent off as part of the query that gets sent over to the server, with the exception of the Database|Show SQL Query statement being modified, in which case processing will occur locally.

d.)Creating a report on a Query file (.QRY) and then adding a parameter field in the Selection Formula of the report will cause ONLY the query to be processed on the server side. The actual filtering of the parameter field selection will occur on the client side.
e.)Do not use IFàELSE conditions in your selection formula, as this also causes processing to occur on the local machine (for SQL databases).
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top