Saturday, December 16

Performance Issues With The Select Expert

Google+ Pinterest LinkedIn Tumblr +

Your selection criteria greatly affect the processing of your report. Not only does it filter out records that you do not need so that you do not have to look at them on your report, but it filters out records so that the report does not evaluate them. For example, there might be 5 million records in your data­base, and you need only 2,500 for a particular report.

How you create your selection criteria impacts whether SCR evaluates it on the client machine (your workstation) or on the database server machine. Because the server is usually a larger, more powerful machine, and because it’s best to send the smallest amount of data over the network as possible, you typically want your server to process the selection criteria formula, rather than your workstation.

Later in the book you will learn more about the role of SQL, the SQL De­signer, and how a selection criterion is used in the SQL statement. For now, I’ll briefly explain how SCR used SQL without you even knowing it.

When using an ODBC type connection for a report, SCR generates a SQL query. It does this all by itself, using the information you entered in the de­sign of the report. When you preview or run the report, SCR sends this statement to your database server, which uses the statement to collect the data you need for your report. If your selection criteria formula is part of this SQL statement, then the server processes it also—which is exactly what you want for fast report processing. If you use just the basic options provided in the Select Expert then SCR includes the selection criteria in the WHERE clause of the SQL statement.

However, if you customize your selection criteria by manipulating the exist­ing formula or writing a new one, SCR processes the selection criteria formula on your client machine. When you deviate from the standard options of the Select Expert, SCR sometimes does not know how to translate the selection criteria formula into SQL, “Advanced Formulas,” discusses some functions that can be used in selection criteria arc included in the SQL state­ment, as does, “Using SQL in Crystal Reports.” If the SQL statement doesn’t include the selection criteria, it is likely because the for­mula contains functions that SCR cannot translate into SQL. In this case, SCR basically copies all relevant records from the database on the server to the client machine and then processes the selection criteria against that set of data on the client machine to filter out the unwanted records.

Customizing your selection criteria sometimes will be unavoidable, and truly lends necessary power-to SCR. Just be aware that your custom formulas some­times force selection criteria to be processed on the client machine, which typically results in slower report processing, You might want to look into database views, stored procedures, or writing your own SQL statement into the SQL query to force the selection criteria to process on the server.


About Author

Leave A Reply