Tuning Progress SQL

[tale103108]

Can someone point me in the direction of documentation on tuning Progress SQL statements? I have a slow running query.
Cheers!

 

[Rob Fitzpatrick]

You didn't mention your Progress version, so the general OpenEdge documentation page is here. It has links to documentation pages for all the supported versions. Within your version, go to the data management collection and you will see manuals called SQL Reference and SQL Development. Chapter 12 of SQL Development is entitled Optimizing Query Performance (at least in the 10.2B manual).

As you will read in the manual, no discussion of SQL-92 performance in Progress is complete without discussing statistics. There are hidden SQL catalog tables in the database that contain table, index, and column statistics. The SQL-92 query engine uses a cost-based query optimizer and it uses this metadata to optimize query plans. However this metadata is not maintained automatically. You have to update the statistics on a periodic basis, especially after large changes like bulk data loads. If your statistics are not up to date, your queries will not be optimized and performance will suffer. Read up on the UPDATE STATISTICS command in the documentation (also Chapter 12).

 

[tamhas]

Aside from statistics, the other global recommendation I would make is to do your joins in the FROM clause, not the WHERE and to think about the sequence of the joins since a tight fit on the first will reduce the number of records that need to be examined.