Crystal Reports 10: The Complete Reference

SQL databases (or PC-style databases accessed via ODBC) present a very different set of performance considerations than do PC-style databases. As has been discussed throughout the chapter, one of the main benefits of SQL databases over PC databases is the ability of the database server to perform record selection and grouping locally as soon as a SQL query is received from Crystal Reports. Only when the database server has performed record selection or grouping itself will it return the result set back to Crystal Reports.

Let the Server Do the Work

As a general reporting rule, you want to always have the database server perform as much of the processing as possible. Database server software is designed and tuned to perform just such operations, and server software is often placed on very high-end hardware platforms to enhance this performance even further. Any glitch that might cause the database server to send back every record in its database (million-plus-record SQL databases are not at all uncommon) to be selected or summarized locally by Crystal Reports will often result in unsatisfactory performance.

To see how much (or if any) of the query is being evaluated on the database server, view the query by choosing Database Show SQL Query from the pull-down menus . Look at the SQL statement for the WHERE clause. If you don t see one, no selection at all will be done on the server ”every record in the database will come down to the PC, to be left to Crystal Reports to sort through. This can be very time-consuming !

Look for an ORDER BY clause if you are sorting or grouping your report. If you see this clause, the database server will presort your data before sending it to Crystal Reports. This can speed up the formatting of your report. If you don t see an ORDER BY clause but you specified grouping or sorting in your report, the server will send records to Crystal Reports unsorted, and the client will have to sort the records.

If you wish to use server-based grouping, make sure you see a GROUP BY clause in the query. This ensures that the server is grouping and aggregating data before sending it to Crystal Reports. If you don t see this clause, check back to the server-based grouping requirements in Table 16-1 earlier in this chapter.

To ensure that record selection occurs on the server, Crystal Reports must convert your record- selection formula to a SQL WHERE clause. Several rules of thumb help to ensure that as much record selection as possible is converted to SQL and that it is done on the server:

• Don t base record selection on formula fields. Crystal Reports usually can t convert formulas to SQL, so most record selection based on formula fields will be passed on to Crystal Reports. While Crystal Reports versions 9 and later have improved on this limitation (most first pass formulas can now be converted to SQL), you ll still want to ensure that any formulas you use in record selection are converted into WHERE clause SQL.

• Don t use the Crystal Reports formula language s built-in functions, such as ToText, in your selection criteria. Again, Crystal Reports usually can t convert these to SQL, so these parts of record selection won t be carried out on the server. You may be able to find a similar function in a SQL Expression field and use the expression in record selection.

  Note	See Chapter 5 for more information on what constitutes first-pass formulas.

• Avoid using string subscript functions in record selection. For example, {Customer .Contact First Name }[1 to 5] = Chris cannot be converted to SQL, so record selection will be left to Crystal Reports. Create a SQL expression instead, perhaps using a SQL LEFT statement, such as LEFT(Customer.Contact_First_Name, 5). Then use it in record selection.

Use Indexed Fields

In Crystal Reports, you ll typically see colored arrows in the Links tab of the Database Expert indicating fields that have been indexed by the database designer. Although you aren t typically required to link only on indexed fields, you may want to attempt links on indexed fields if possible anyway. Depending on the database system that you re using, linking to non-indexed fields or performing record selection on non-indexed fields can prove detrimental to the performance of your report. Most SQL database systems have indexing capabilities that the database designer probably considers when designing the database.

If at all possible, work with the database designer to ensure that indexes are created to solve not only your database application needs, but your reporting needs as well. Try to use indexed fields as much as possible as to fields in linking, and for record selection.

Also, it may improve performance to select records using as many fields as possible from the main or driving table. For example, you may have a main transaction table that is linked to five lookup tables, and you may want to use the descriptions from the lookup tables in record selection. Instead, consider using the fields in the main table for record selection (even if you have to use the codes in the main table, and not the descriptions from the lookup table). Again, if these fields in the main table are indexed, you ll probably see a performance improvement.