Crystal Reports  

Pushing Down Record Selection to the Database Server

The most important thing you can do to speed up report processing is to limit the amount of data that is returned from the database. Your primary tool for doing this is the record selection formula.

The drivers provided with Crystal Reports for SQL data sources allow "pushing down" record selection to the database server. When you specify a record selection formula in a report based on an SQL data source, Crystal Reports analyzes it, generates a SQL query from it and passes the SQL query to the database server. Record selection is then carried out in two stages:

Since database servers are usually faster machines than workstations, it is to your advantage to specify record selection formulas that can be processed by the server in the first stage. Doing so minimizes record selection on the local machine in the second stage. The following kinds of record selections can be pushed down to the server:

Pushing Down Record Selection — An Example

This example demonstrates the benefits of writing record selection formulas that can be pushed down to the database server.

In the Orders table of the Xtreme sample database, there are 2,192 records, of which 181 have order dates prior to 2001. Suppose you want to report on only those records. On the one hand, you could use this record selection formula:

Year ({Orders.Order Date}) < 2001

The SQL query generated will send all 2,192 records to Crystal Reports, and then the record selection formula will reduce this to 181. To see this, click Show SQL Query on the Database menu and notice that the SQL query has no WHERE clause. This is because Crystal Reports is not able to push down the Year ( ) function in the WHERE clause.

On the other hand, this record selection formula generates the same report:

{Orders.Order Date} < #Jan 1, 2001#

This second formula, however, can be performed on the database server, so it is pushed down. The SQL query generated will send only 181 records to Crystal Reports. So, when the record selection formula is evaluated by Crystal Reports, no further records need to be eliminated. Click Show SQL Query on the Database menu and notice that the resulting SQL query has a WHERE clause.

As this example shows, your report's processing speed improves when you enhance your record selection formula. In this case, both formulas generate the same report, but the second takes advantages of the power and optimizations that the database server can use when handling its own data.

Record Selection Performance Tips

Consider the following performance-related items when setting up record selection requests:

General

SQL Databases

See Also

Basic Record Selection | SQL Expressions