Optimizing Field Selection
The SELECT statement determines which fields are selected from the tables and sent to Crystal Reports. Only fields listed after the SELECT keyword can be printed on the report. There are two ways to improve performance here. The first is to only select the records that you will print on the report. Fortunately, Crystal Reports does this for us by specifically listing each field on the report individually after the SELECT keyword.
The second method for improving performance is to have the database server do the work of calculating your formulas on the server. For example, if you have a formula in your report that calculates sales tax, you could have the database server do this calculation for you instead of Crystal Reports doing it. The database server is more optimized to process calculations.
The way to make the database server calculate the formulas is by using SQL expression fields. These are formulas built using only SQL functions. By limiting yourself to just SQL functions, Crystal Reports can pass the entire formula to the database server for processing. Crystal Reports doesn’t have to do any of the work.
The one caveat to this is that the SQL function library is limited in scope. Many Crystal Reports formulas can’t be converted to SQL expression fields. If you’ve been creating formulas in Crystal Reports for very long, you’ve gotten spoiled by all the great functionality built into the software. In fact, there aren’t many programs on the market that give you so many built in functions. For example, Crystal Reports has dozens of functions for performing financial calculations and handling fiscal dates. But in most programs, you have to create these functions yourself. The same goes for database servers. You are limited to how much you can really do here. We are going to talk about SQL expressions in much greater detail later in the chapter, so we will focus more on what they can and can’t do there.
The one good thing to consider is that Crystal Reports is already very efficient at calculating formulas. Converting all your formulas to SQL expressions isn’t a high priority for optimizing your reports. In most cases, there won’t be a huge performance gain. The critical time you want to do this is when you are processing reports involving tens of thousands of records or more.
