Optimisation of SQL Query

One of the biggest issues facing a Crystal Reports developed is the performance of the report. Many times I've heard users complain that a report takes too long to generate. A good approach to take to optimise performance is to analyse the SQL Query. In Crystal Reports you can view the SQL Query by going to the Database menu and selecting 'Show SQL Query'. You can extract this query and run it through a query analyser.

For Oracle databases you can use Toad to 'explain the plan'. This can tell you what indexes are being used or if the query does a full table scan to get the required data. Always try to make use of the indexes. Sometimes it may be worthwhile creating new indexes on the tables. In some cases you can add additional conditions to the selection formula record which will cause the database optimiser to select a different set of indexes.

At times there may be nothing more you can do so you should speak to your database administrator to see if they can improve the performance on the database side. There was a case where a report took a long time to run because it was not selecting best index. This was because the sample size was too small. The database adminstrator has set a sample size of 2000 records and this was used to specify which index to use for a table of 6 million records.