Crystal Reports 10: The Complete Reference

In many cases, record selection is the most time-consuming portion of the report process, particularly with larger databases. If you re using a PC-style database located on a local or network hard drive, Crystal Reports performs the record selection itself, reading every record in the database and keeping only those that match. If you are using a server-based database (such as SQL Server or Oracle), Crystal Reports will attempt to create a WHERE clause in the query that s sent to the database server, which will cause the database server to perform the query and send only the desired records to Crystal Reports.

In either situation, you ll probably see overall improved performance if you use indexed fields for your record selection (with PC-style databases, this is often very critical). Indexed fields are fields that are specially designated when the database is designed. The field s index stores all the values in the field in a presorted state that makes it much faster to select records based on the field. To determine if a field is indexed, you may wish to consult the database designer.

You can also see which fields are indexed by using the Links tab of the Database Expert. Click the Database Expert button on the Expert tools toolbar, or choose Database Database Expert from the pull-down menus . Then, click the Links tab (you must have added at least two tables to your report to see it).

You ll notice a small tent character appearing next to every indexed field (the different colors you may see are generally insignificant to record selection, as long as you select on a field with the symbol). Make note of the indexed fields and attempt to use them in record selection. If the field you need to select on is not indexed, and record selection appears very sluggish , you may wish to consult the database designer about adding an index for that field.

Finally, double-check the setting of the Use Indexes or Server for Speed option. To check the option for the current report, look in the File Report Options dialog box. If you wish to check the option for all new reports in the future, look for the option on the Database tab of File Options. If this is turned off, Crystal Reports won t use field indexes at all.

SQL databases (or PC-style databases accessed via ODBC) present a different set of performance considerations when compared to performance with PC-style databases. As a general reporting rule, you want to always have the database server perform the record selection (via the aforementioned WHERE clause), if at all possible. This can typically be accomplished by using only the Select Expert to create selection criteria ”using the Formula Editor makes it entirely too easy to introduce functions that Crystal Reports can t move to the database server. Also, making changes to what the Select Expert creates with an Is Formula operator or the Show Formula button may seriously degrade database server record selection performance. As with PC-style databases, make sure the Use Indexes or Server for Speed option is turned on in File Report Options.