CML Data-Services Performance

Performance in the data-services tier results from features of both the relational database and the storage engine. The following database development techniques can enhance performance in this area:

• Design and implement a normalized logical database design. The CML database is normalized.
• Evaluate the most frequently executed queries. As a rough guideline, when frequently executed queries have four-way joins, the process of further normalization should be reevaluated. Highly normalized databases are characterized by large numbers of narrow tables and numerous complex relational joins that can hurt performance. The queries of the CML application, queries in stored procedures and embedded SQL, are executable without excessively complex joins.
• Use identity columns as surrogate primary keys. The alternative is often a composite key, a key that requires multiple columns to uniquely identify the row. Identity columns, a narrow index, have more rows per page and fewer index levels, thereby boosting performance. The CML database tables use identity column primary keys extensively.
• Use temporary tables in the database to store the results of search queries. When the F & M employees submit searches to the CML database the results are stored in temporary tables. When the user clicks the Back and Forward buttons of the browser no queries are repeated. The application retrieves and redisplays results from previous queries that are stored in existing tables. The tables are deleted when the session is no longer active.
• Use stored procedures to execute queries. Stored procedures are compiled and they execute more quickly than noncompiled queries.

Add and delete indexes to tune performance. Because the indexes are not considered part of the logical database, they can be added, dropped, and changed without impacting the database schema or application design in any way other than performance. Clustered indexes can increase performance tremendously. One clustered index is available per table. The clustered index should be a column with fairly static information of reasonable diversity, and often listed in the WHERE clause. No CML database tables use clustered indexes.