For faster reports, create summary tables using SELECT INTO.

Our client had a report that needed a lot of time to process. We were shown a report that needed 3 to 4 hours on a slow Saturday to do its best. It was a report that included information about Products, Bill of Materials, and the last 12 months sales to identify inventory levels and stock turns. 

“Could you make it run faster?” they asked.

Some initial improvements helped a little bit, and then we looked deeper into the reports inner workings. There was a subreport for each product that looked at the sales for the last 12 months to calculate the average monthly sales over that period. 

When you consider the subreport calculation, you realize that the number changes only once a month. So if we could do that processing on the first of each month, then the report would not need to do the same processing each time it ran.

SQL includes a neat SELECT INTO statement you can use to create summary table. We could then schedule the SQL to run on the first of each month. The SQL reads as:

SELECT StockCode, Min(SalesDate) as FirstSale, Sum(SalesQty) as TotalQty 
INTO SalesSummary 
FROM SalesTrans 
GROUP by StockCode 
WHERE SalesDate >= GetDate() – 365 

The SELECT clause identifies the fields we want in the summary table:

• StockCode is the product code number

• Min(SalesDate) is the date of the first sale for that product

• Sum is the Quantity sold.

• “As” lets us rename the fields to a name we would like to use in the new summary table.

• INTO SalesSummary identifies the name of the new table to create. 

• FROM, GROUP BY and WHERE are the standard SQL clauses you would use in a normal SELECT statement.

You can take a standard SELECT statement with FROM, GROUP BY, WHERE and joins to create a temporary recordset and then make it permanent by adding the INTO clause.

We removed the subreport and joined the summary table (SalesSummary) to the Stock table in the main report. Using this technique, the report now runs in less than 30 seconds. A big improvement on the previous requirement of 3 to 4 hours.

We then scheduled the SELECT INTO using SQL Servers Job Processing, so it now runs on the first of each month and builds the summary table we need.

The only catch we hit is that SELECT INTO will fail, if the table already exists. So we have a two step process in our automated task. The first removes the table with a “DROP TABLE SalesSummary”, while the second does the SELECT INTO. Now our user just needs to find some other things to do on those Saturday mornings instead of running the report.

The best thing you can do to speed up your Crystal Reports is to get a copy of Report Analyzer, so you can see exactly where the problems are. Too many people waste time farting around with trial and error, when they could--and should--fix the problem quickly, instead.

Do you have your own tips? Write to webmaster @ crystalkeen.com and we'll post your tips with a link to your Website or with some other attribution if you choose.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.