Designing the Data Structure › How to Complete the Data Structure Design › Optimize the Data Structure Design

Optimize the Data Structure Design

In most cases, CA Gen supports tuning database performance without changing the data model. Changes should be reviewed with a specialist for the target database.

The following list details some of the optimizations that can be performed in the Data Structure List:

• Adding and deleting indexes-Adding new indexes will result in new index definitions in the DDL.

  Analyzing the access paths used in procedures will determine a set of entry points that allow the DBMS to traverse the database as efficiently as possible.

  After the system has been designed, it is possible to remove any unused indexes from the Data Structure List.

• Denormalizing-CA Gen also supports a technique called denormalization, which involves replicating fields from one record into another, related record.

  Logic generated by CA Gen automatically maintains the correct values of data replicated in this way.

• Specifying locations for tables-Some DBMSs support specified location of tables on pages and different storage devices. The locations of tables can be specified in CA Gen.
• Making changes specific to the DBMS-The changes that can be made to optimize the design depends on the target DBMS. For example, DB2 allows the clustering of records from different tables to the same tablespace to improve access to related records.

Some optimization needs to be performed before procedure design begins, but some can only be completed after the detailed design has been completed. The DBMS expert will need to make a least two “passes” over the Data Structure List and Data Store List before the physical definition of the database is converted into DDL statements.

The optimizations should be performed to increase performance for the predominate processing pattern, since for every gain in one area, losses may be sustained in others.