Two different customers contacted me recently with similar questions.

The first customer had a subreport that connected directly to the tables. She thought she could speed it up by changing the subreport to use a SQL command instead of tables.  But the command made the subreport take even longer.

The second customer asked me why her ERP vendor had created all of the reports using tables instead of using views and stored procedures. She quoted her consultant who said, “it’s like sending the whole mine to Crystal instead of a wheelbarrow.”

But I never assume that handwritten SQL (view, stored procedure or command object) will improve the performance of a report. There are some situations where writing your own query can dramatically change the performance of the report.  And sometimes the  requirements can only be met by writing the query directly in SQL. But these are in the minority in my experience. In most cases I have found that the performance of a table-based report will be comparable to the performance of other methods. This is because Crystal Reports can generate workable SQL based on your linked tables and selection criteria. To increase the performance you would have to write a better query than the CR engine, and there isn’t always enough improvement to make this worthwhile.

Now take the subreport example above. The subreport using a command should have been roughly the same as the original table-based report in terms of performance. The reason this customer saw such slow performance when using the command was because she put the subreport parameters into the selection formula instead of directly into the command WHERE clause. So the command couldn’t apply the key criteria and had to “send the whole mine” back to Crystal. We could have fixed the command, but all that effort would have put the performance level back where she was with tables, so we decided to go back to the tables.

So what that consultant describes really can happen – but it isn’t directly related how you connect. Instead, I usually find that this problem occurs when the selection formula uses functions around the database fields.  This can prevent the criteria from passing to the SQL. You can often fix this by changing your selection formula so that the functions are used with the literal values rather than the database field. In many cases it only takes a few tweaks to make a table based report behave like a report using custom SQL, without losing the flexibility of using the tables directly.

So, if you have a report that is taking to long, or if you want to know how to optimize your report using tables or SQL, give me a call.