In most cases I find it more efficient to let Crystal generate the SQL for reports.  This makes the reports simpler for most CR users to create.  It is also easier to add additional fields, and the Visual Linking window makes the query structure easier to understand. Finally, in most cases, the performance is essentially the same since CR can generate workable SQL.

But there are plenty of cases where the only practical solution is to base the report on a SQL Command, instead of on linked tables. When I need to do a UNION of two or more queries, or when I need to apply a filter to the ‘outer’ table of an outer join, I have to resort to SQL Commands.  I am not very fast at writing SQL so I usually create a regular report first, using the correct tables, joins and fields. Then I copy the SQL that CR generates and use that as the starting point for my command.  Recently I find myself using SQL more, so I have started to pick up tricks that make SQL queries work faster.

Case #1

Last week I was working on a query that combined several fairly large tables.   The query was taking several minutes to return data and we were trying to see if there was anything we could do to speed things up.   I noticed that one of the key criteria fields was coming from the last table added in the FROM section of the query.   I decided to do an experiment and moved that table so that it was added first in the FROM.  Then I moved the criteria for that table from the WHERE Clause to the FROM clause, making it part of the “ON” rules for that table.  My theory was that each table added will typically multiply the number of records in the results.  So if the primary filtering is done on the first few tables in the query it reduces the number of records that get multiplied as the later tables are added.  Sure enough, we saw a dramatic improvement in the performance.  I decided to move all the other rules that I normally put in the WHERE clause and make them part of the FROM clause.  We ended up with a much faster report.

Since then I did some research and found that the idea of putting filters in the FROM rather than the WHERE seems to be common knowledge among DBAs.  I was surprised that I had never heard this before.  I do know that some of the more heavy duty databases try to optimize the query automatically.  But that certainly wasn’t happening in this situation.

Case #2

Today I got a very similar result in a report that used tables directly.  This report was designed to take one order, look up the inventory items on that order, and then generate from up to 5000 label copies for that item based on the order quantity.  We had added an “inflation table” to the report containing 5000 rows, which would force the label to duplicate as many times as we needed.  But when we ran the report it never completed the query.

So I looked at the SQL and noticed something.  Even though there was a rule in the WHERE clause to select one order,  the Orders table was being added at the very end of the FROM clause.  It was added even after the Inflation table.  That meant that before the database could apply the filter to select the one desired order, it had to assemble the entire Inventory table, then repeat every inventory item  5000 times.  Only THEN could it start looking for the desired order.  With thousands of inventory items, each multiplied by 5000 records in the Inflation table, there were millions of unnecessary records being generated.  I reversed the  joins in the CR linking window to put the Orders table first, then the Inventory table and then the Inflation table.  My theory, again, was that the database would first narrow the search to one desired order, then look up just the inventory items on that order.  Only THEN  would it need to multiply the result by 5000.  The modified report finished in just a few seconds.  I could have probably sped it up further by converting the report to use a command and moving the WHERE criteria to the FROM, but the performance already satisfied the customer.

So if your reports are taking longer than you think they should, you might try some of these ideas.   If you need help I would be happy to set up a short consult to take a look at your report.