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:02SelectExpertRecord

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.05ShowSQLQuery

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.

Share on FacebookShare on LinkedInTweet about this on TwitterShare on Google+Email this to someone

The Famous Rubber Duck

The rubber duck was inducted into the National Toy Hall of Fame this year!

Check out the details here http://www.toyhalloffame.org/toys/rubber-duck.

Of course, we think it’s cool. We put a lot of thought into our selection of rubber ducks each year. Do you have a favorite?

Share on FacebookShare on LinkedInTweet about this on TwitterShare on Google+Email this to someone

New Report for Web Customer Feedback Program

If you are using the Web Customer Feedback Program Module, you’ll be interested in the new report to see your statistics for a date range. The links in blue in the report allow one or more levels of drill-down that can be used to view and analyze your customer feedback. 

Share on FacebookShare on LinkedInTweet about this on TwitterShare on Google+Email this to someone

1 (888) 667-7332

Contact Us

Literature Downloads


Contact

Corporate Headquarters
Morse Data Corporation
16 Pierce Street
Dover, NH 03820
Toll Free: (888) 667-7332
Phone: (603) 742-2500
Fax: (603) 742-8178
Technical Offices
Morse Data Corporation
9661 W. 143rd St. Suite 200
Orland Park, IL 60462
Toll Free: (800) 860-9515
Phone: (708) 873-0010
Fax: (708) 873-9967

About