Use SQL expressions to improve performance

Your reports will refresh faster if you add a little SQL

One of the best ways of boosting performance in Crystal Reports is to use SQL expressions. In favourable cases, a SQL expression can dramatically cut the time needed to refresh a report. It can also reduce network traffic and thereby improve the performance of the network as a whole.

At base, SQL expressions are very much like the more familiar Crystal formulae. You can place them directly in the report, in record and group selection criteria and in sort order specifications. The main difference is that they are written in Structured Query Language (SQL) rather than the Crystal formula language. Unlike Crystal formulae, SQL expressions are actually executed by the database server.

Note that SQL expressions are not the same as SQL commands. The latter behave more like a data source than a formula. For more about the benefits of SQL commands, see Use SQL commands to solve reporting problems and speed up Crystal Reports.

The slow way

SQL expressions can be especially beneficial when they are used in record or group selection criteria, as the following example will show.

Suppose you have an orders table which includes an Order_Date field. You want to select records according to the day of the month on which the order was placed. If you didn't use SQL expressions, you might write the following Crystal formula to return the required day number:

Day ({Orders.Order_Date})

You would plug this formula into the Select Expert, specifying that you only want to include records where the formula equals a given number - either a hard-coded value or a parameter value. When you close the expert and refresh the report, you will see the required sub-set of the orders.

If you now go to the Database menu and select Show SQL Query, you will see an SQL statement similar to the one in Figure 1. This is the SQL code that CR uses to request the records it needs from the database. The important point about this statement is that there is no WHERE clause. That means that all the records in the table will be retrieved from the server, regardless of whether they are needed for the report. It is up to CR to select the required records.

Figure 1: When selection is based on a Crystal formula, the SQL statement lacks the vital WHERE clause.

The fast way

Now, let's see what happens when you use a SQL expression instead of the formula. The mechanics of this are straightforward: start by selecting SQL Expressions in the Field Explorer, click the New button (or press Ctrl+N), specify a name for the expression when prompted to do so, and finally type the expression in the SQL Expression editor, which is very similar to the Formula Editor.

However, instead of using Crystal or Basic syntax for the expression, you must code the expression in SQL - and in the particular dialect of SQL which the server will understand. If you were using SQL Server as your database, for example, you wouldn't be able to use Crystal's Day() function to extract the day of the month; you would have to use the T-SQL Datepart() function instead:

Datepart(dd,Orders.Order_Date)

Not only is a different function used to obtain the day number, the curly brackets round the field name are omitted as well. That's not a mistake. The use of curly brackets to delimit a field name is part of CR's syntax, and SQL Server would generate an error if you used them in this context.

Now that you have created the expression, you can use it in the record selection criterion in place of the Crystal formula. So open the Select Expert again, delete the existing criterion, and create a new one using the SQL expression.

After you have closed the expert and refreshed the report, you should see exactly the same records as before. However, if you now choose the Show SQL Query command from the database menu, the SQL code will look something like that shown in Figure 2.

Figure 2: The SQL statement now has a WHERE clause, so fewer records will have to travel across the network.

The big difference is that there is now a WHERE clause. This means that the record selection will be done on the server, and only records which meet the criterion will travel across the network. With a small table - a few hundred records or so - you might not notice much of a difference. But if the table contains tens of thousands of records, and if you only want to select a small percentage of them, the performance gains could be substantial.

Disadvantages

The biggest disadvantage of SQL expressions is that they are database-specific. You have to be familiar with the SQL dialect and syntax used by the database server, and if you ever move the report to a different database, you might have to re-code some or all of your expressions.

In addition, SQL expressions are not available for all types of databases. Crystal Reports only supports them for what it calls 'SQL databases', such as SQL Server and Oracle, as opposed to 'PC databases' like Access. If the report is based on a PC database, you won't see an entry for SQL expressions in the Field Explorer.

Furthermore, the main benefit of SQL expressions is only available with true client/server databases, such as SQL Server, Oracle and Sybase, where the processing is performed on the back end. With file server-based SQL databases, such as Visual FoxPro, Crystal Reports permits SQL expressions to be used, but they won't deliver the same performance gains. CR will send the correct SQL code, complete with WHERE clause, to the database, but the actual record selection will be performed by the ODBC driver or OLE DB provider on the local machine. So you don't get the benefit of reduced network traffic.

Despite these drawbacks, SQL expressions are definitely worth adding to your armoury of reporting techniques. If you are working with large tables in a true client/server environment, you will be pleasantly surprised by the improved speed of your reports. Give them a try.

Mike Lewis Consultants Ltd. February 2004.