Now that we've gotten all the caveats and qualifications out of the way, here are some of the things we have uncovered during the course of our testing and prototyping.

The critical factor
The single most important item which affects data retrieval from an RDBMS is the amount of RAM in your server. This trumps everything else you can think of: indexes, page size, CPU speed, etc. The reason for this is that as the RDBMS fetches pages from disk, it caches them directly in core memory; the next time you need that same piece of data (assuming it has not changed) the RDBMS fetches it directly from RAM. So if you have 2 Gig of data in your MEASURES table and 4 Gig of RAM, you're going to see decent response times across the board.

N.B. Please note that recently (October 2010) we have seen situations in SQL Server where adding additional RAM did not significantly improve performance. If you are still getting poor performance in SQL Server even if you are not swapping pages, refer to the discussion below on SQL Server Indexed Views.

Which RDBMS performs best?
SQL Server and Oracle seem roughly equivalent, while DB2 is somewhat slower. For example, if you run the DDL/SQL that creates the base PMF datamart (in PMF 5.2.2 you can find this in the \apps\pmfdbms\Create_Database folder), it will take about 15-20 seconds on SQL Server and Oracle. On DB2/UDB the same code takes over a minute to run. Once your PMF data is loaded, the difference is not as extreme, but it is still noticeable. The average report takes between 10-25% longer to run on DB2. It is possible that there are some DB2 tuning parameters that we are not configuring properly when we're doing this testing, so your mileage might vary.

What happens when my database does not fit into RAM?
Once your data exceeds your available RAM you will start seeing significant performance degradation. You would think that adding additional indexes on the MEASURES table would speed up performance (as we did), but this is not the case. We tried many different combinations of indexes - adding indexes on the individual DIMENSION_ID columns, adding indexes on combinations of DIMENSION_ID columns, etc. We even tried bit mapped indexes where the cardinality was low. However, at best we saw maybe 5%-10% improvement; and this was not satisfactory.

If you are running on Oracle, the recommended solution is to create Materialized Views (MVs) which sum up the Actual and Target values on the higher dimension levels. On DB2 there is an equivalent technology called Summary Tables (or Automatic Summary Tables - AST). MVs yielded excellent performance improvements. For example, with 10M rows in the MEASURES table, a report summing up all Measures on a Quarterly basis was taking over a minute; after we added Materialized Views the same report ran in several seconds.

For SQL Server, Indexed Views will provide the same level of functionality as Oracle MVs. In our internal testing, response times on a 15 million row database went from 16 seconds to 0.2 seconds when we added an Indexed View.

Comments Please
This finishes up our little series on Reporting Response Times. If you have any observations, comments, or suggestions, please do not hesitate to respond back. We are eager to hear about your experiences.

EricH

This message has been edited. Last edited by: EricH,