Performance Monitoring and Tuning

As reports grow in data size and complexity, ensuring optimal performance becomes increasingly important. This section serves as both a reminder of some performance tips already covered in the book to this point and as an introduction to some other tools and methods provided by Crystal Reports to optimize report performance in demanding environments.

Group By On Server

This Crystal Reports option enables you to push down the Grouping and Sorting activities to the database server. By performing these functions on the database instead of the Crystal server, less data is passed back to the Crystal Report and report-processing time is decreased. This option can be set locally under the Database main menu when the given report is being edited, or set globally on the Database tab of the Options menu accessed under the File main menu.

Some restrictions apply to the use of this option, including the following:

• The data source must be a standard SQL database.

• The report must have groups within it and the groups must be based on database fieldsnot formula fields.

• The groups cannot contain specified order sorting.

• The details section of the report must be hidden.

• Running Totals must be based on summary fields (that is, they do not rely on detail records for their calculations).

• The report cannot contain Average or Distinct Count summaries, or use Top N values.

When this option is applicable and used, the involved reports will perform faster. In addition, the detail level on these reports is still accessible through the standard drill-down functionality and will make dynamic connections to the database to bring back any user-requested detailed information.

SQL Expressions in Record Selections

As referenced and discussed previously in the book, SQL expressions are SQL statements that provide access to advanced database aggregations and functions. Using SQL expressions wherever possible in record selections and formula creation (versus using Crystal or Basic syntax) optimizes the amount of work that will get processed by the database server (versus the Crystal server)and this will increase your report's performance.

Some quick examples of SQL expressions that can be used in place of Crystal formula syntax:

Crystal Formula Syntax	SQL Expression (SQL Server Syntax)
IF/THEN/ELSE	CASE [Database Field]
Or	WHEN Condition THEN Value1
SELECT CASE	ELSE Value2 END
Concatenate (x + y)	CONCAT([Database Field1], [Database Field2])
MONTH(datefield)	MONTH([Database Field])

You should investigate the SQL capabilities of the report's database thoroughly when report performance and optimization becomes a critical business issue. Mature databases like Oracle, DB2, SQL Server, and so on have mature SQL capabilities that can often be leveraged in lieu of the Crystal formula language in field selection and record selection. Using SQL expressions can dramatically increase report performance in many instances.

Use Indexes on Server for Speed

This is another performance option that is set under the Database tab of the Options dialog accessed from the main File menu. This option ensures that the involved Crystal Report uses any indexes that are present for the selected database and for the given report.

On-Demand or Reduced Number of Subreports

As discussed in the chapter on subreports, these objects are reports unto themselves and maintain their own database connections and queries. As you can imagine, if too many subreports are added to a main report, this can lead to runaway report-processing times. A typical scenario where this might happen is when you want to include the data inside a subreport for every group within the main report. In a large report with hundreds or even thousands of groups, this can lead to that subreport running thousands of timesa palpable performance hit even when the subreport is small and/or optimized.

To minimize this challenge, it is a good idea to ensure that in-place subreports (as opposed to on-demand subreports) are used judiciously and that they are indeed required in performance-sensitive reports. Often times, only a very small subset of the subreports are ever viewed by a user and an acceptable user experience can be provided with On-Demand subreports instead.

Performance Monitor

After a report has been functionally designed, Crystal Reports provides the Performance Information tool to facilitate performance testing. This tool provides information that helps in optimizing the current report for fastest performance. The Performance Information dialog shown in Figure 11.5 is accessed from the main Report menu.

Figure 11.5. The Performance Information window provides detailed report performance metrics.

The left side of the Performance window provides a tree structure that facilitates navigation among the different report metrics areas maintained by this tool:

• Report Definition This node provides information about the content of the report: the number of fields, the number of summaries, UFLs (User Function Libraries), Chart objects, and so on. Each of these objects will have some impact on the performance of the report dependent on their quantity and complexity. The Page N of M Used option is relevant because it specifies whether a third pass of the data is needed when processing this report. If not required, this can be eliminated by removing any Page N of M special fields on the report.

• Saved Data This node provides information about the data captured in the involved report: the number of data sources used, the total number of records, recurring database record length, size of saved data, and so on. These metrics are of particular relevance when Group By On Server is properly used but can be generally used to monitor the effects of report changes.

• Processing This node provides information about the processing of the selected report: Grouping on Server?, Sorting on Server? Total Page Count required?, Number of Summary Values, and so on. The metrics provided here have a clear impact on performance and can be used to monitor the effective implementation of the optimization techniques described in this section.

• Latest Report Changes This node provides information about recent changes to the report to facilitate performance monitoring.

• Performance Timing This node provides the timing metrics based on opening the involved report and formatting its pages. These metrics provide the ultimate benchmark to determine the effectiveness of any implemented report optimization techniques.

Additional tree branches and nodes are displayed if the involved report contains subreportseach of these nodes will appear under a new parent node for each subreport facilitating performance analysis at a granular level.

One final note on performance monitoring: to facilitate record-keeping on the progress of any ongoing database or report optimizations, the Performance Information window provides the capability to save the involved report's performance information to a file for future reference and time comparison.