Making Reports Run Faster When Comparing a DATE to a DATETIME in SQL
When Crystal Reports runs a report, it retrieves as much information as it can from the SQL Server and brings it back to the local machine to finish processing. Depending on the complexity of the report, this can mean a lot of processing taking place on your PC. As your database grows, you may notice that your reports aren’t running as fast as they once did. One way to speed up the running of a Crystal Report is to push as much as possible of the work down to the SQL Server.
There are a number of different ways to do this, not all of which we’ll discuss here. If you have a SQL professional in-house, you may choose to write the entire report selection as a SQL stored procedure or view and just using Crystal Reports as a viewing interface to display the results. If you’d rather keep the “building blocks” of the report in Crystal Reports, make sure your record selection is done wisely; don’t use formulas or data conversions on fields within the record selection. You can tell you’ve done this when you see most or all of your record selection in the WHERE clause of the “Show SQL Query” menu option.
Here is an easy example using dates.
For most reports that have a date selection, you only want to choose a beginning and ending date, not date/time. But, most date fields in InOrder are date/time fields. Comparing date to date/time is a data conversion.
Here is a pretty common record selection, converting the date/time of an InOrder field to Date, and comparing it to the input parameters for start and end date:
Here is its corresponding SQL Query. Note that it is not asking SQL Server to do any filtering of the results by date.
Now here is the same record selection using a SQL Expression.
Here is the corresponding SQL Query. It’s asking SQL to do the work to filter by date.
You can make your own SQL Expression to carry out calculations and conversions in the Field Explorer of Crystal Reports.
Any time you need to use a formula or data conversion for record selection, sorting, grouping, or totaling – you may want to consider building a SQL Expression instead.
No Comments