Database Performance Considerations
The following guidelines describe some best practices you can adopt to:
- Achieve high performance when using SuperCHANNEL to convert your source data to SuperSTAR format.
- Achieve high performance when cross tabulating the data in the SuperSTAR clients.
Guidelines for Improving Conversion Performance
| Recommendation | Explanation |
|---|---|
| Infrastructure | Run SuperCHANNEL on a computer with the appropriate specification. See Installation Requirements, Compatibility and Supported Platforms for more information. |
| Use JDBC rather than ODBC | Use a JDBC driver for the source database rather than an ODBC driver. Using an ODBC driver often adds an unnecessary overhead to the channelling process. See JDBC Drivers for more details on configuring the appropriate driver for your RDBMS. Note: There is a Text Database Definition (TDD) driver that is a JDBC driver available with SuperCHANNEL. Text files are useful when your data is stored in mainframe or legacy systems. |
| Avoid channelling across a network | When possible, build the target database on the same machine as the source database. Channelling across a computer network will slow the performance of the target database build. |
| Use multi-threading | If you have two CPUs on the machine that SuperCHANNEL is installed on, use the multi-threading option. In most cases this will almost halve channelling times. |
| Use batch mode | Where possible, SuperCHANNEL uses batch mode to insert groups of rows into the SXV4 database rather than one row at a time. This can substantially reduce the database build time. Where possible, design your source database so that it only uses features that are supported by batch mode:
|
Guidelines for Improving Cross Tabulation Performance
Cross-tabulation time occurs when SuperSTAR performs tabulations. You can make changes to your source data in the underlying database structure that will affect the tabulation performance.
To increase cross-tabulation speed:
- Sort the data by primary key in the top level fact table.
- Sort each child fact table by its foreign key.
What If I Have Multiple Fact Tables?
In some cases, you will need to consider what type of reports users will want to run against the database, and then sort accordingly.
For example, you might have two fact tables (Fact Table 1 and Fact Table 3) that both reference a single child table (Fact Table 2):
In this case you cannot sort two foreign key columns in the child table.
You need to consider the following questions before choosing which foreign key to sort on:
- What reports will be produced from the data most often?
- How many records are contained in each table?
The result will be to either:
- Sort on Fact Table 2's foreign key 1 to complement the sort on Fact Table 1's primary key.
- Sort on Fact Table 2's foreign key 2 to complement the sort on Fact Table 3's primary key.