Friday, September 23, 2005

Performance improvements and SQL optimizations

I was reading Mark Rittman's blog post on a Discoverer best practices presentation (by Mike Donohue of Discoverer product management) he attended at Open World. In particular he mentions
Discoverer 10.1.2 has some "patented" optimisations for "join and item trimming", which means that if you usually build your folders as custom folders, with a "select * from customers" type query, these optimisations won't be used (they only work on simple or complex folders, where Discoverer itself puts the SQL together) which is a good reason now to stop building folders like this.
A small correction - these performance improvement were first introduced in version 9.0.4, and have been in the product for about a year and a half now. These performance improvements spanned three different but related areas in the way Discoverer generates the SQL for a worksheet query:
  • Item trimming - where items not needed in the worksheet are excluded from the generated SQL.
  • Join trimming - where table/view joins not needed to satisfy a query are removed, but integrity constraints are maintained.
  • Inline view flattening - reduction in the number of inline views used in the SQL query generated.
Some minor changes were made to the Discoverer Administrator UI, but for the most part no worksheet modification or other change is needed to make use of these optimizations. Take a look at the doc (Oracle® Business Intelligence Discoverer Administration Guide, 10g Release 2 (10.1.2.1), Part No B13916-04) for more details.

During some internal benchmark studies we found dramatic improvements in the size of the SQL sent to the database, parsing times, and execution times. Of course, not all queries would benefit from these improvements, but for large queries, especially the kinds that you would expect to find in Oracle Applications schemas, the performance gains can be quite substantial. And as Mark points out, the fact that custom folder cannot make use of these optimizations is another reason not to use them unless you absolutely have to.