IDMS: How to define an index to get a SQL query to perform more efficiently

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

In some cases adding an index to the database can enable an SQL statement to perform more efficiently. This document summarizes the considerations involved.

 

Environment

IDMS - all supported Releases

Resolution

Sometimes the performance or response time for a SQL query does not meet the run-time response requirements. One option in this situation is to define or alter an index to provide another option for run-time access. Here are tips on how define an index in order to improvement performance for a SQL query.

One thing to remember is that although adding another index may minimize the number of I/Os required to retrieve data from the database, having too many indexes could cause performance problems in other areas since there is some overhead associated with storing and modifying the index.

Therefore you need to evaluate the importance of the query, the execution savings, etc., against the required overhead, to determine what is the correct choice for your site.

If you are considering adding an index, examine the following:

• Does the query name specific columns in the column list and/or the selection criteria?
  
  
• Does the query specify a sort sequence for the rows returned (ORDER BY or GROUP BY clause)?

If the answer to either of the above questions is 'yes' then it may be possible to provide faster response time by adding an index to the database. If you want to add an index tailored to meet the needs of this particular query, the sortkeys of the index should match the columns in the selection criteria (or the column list); and the sort order for the index should match the sequence specified in any sort clause in the query. This is most likely to be a viable option in the following situations:

• This is a critical query, and improved performance is essential for application success; or
  
  
• The query is performed frequently in the production environment; and
  
  
• Adding the index does not impose an inordinate additional administrative burden.

The CA IDMS Database Design section  'Adding Indexes', has a chart that can be used to compare index efficiency versus the potential impact to the IDMS system.

+---------------------------------------------------------------------------+
? Efficiency      ? Potential Impact                                        ?
? Considerations  ?                                                         ?
+---------------------------------------------------------------------------?
? I/O             ? I/O may be reduced for retrieval but increased for      ?
?                 ? update.                                                 ?
+---------------------------------------------------------------------------?
? CPU time        ? CPU can be reduced for retrieval but increased for      ?
?                 ? update.                                                 ?
+---------------------------------------------------------------------------?
? Space           ? Indexes require extra storage space in the              ?
?                 ? database.                                               ?
+---------------------------------------------------------------------------?
? Contention      ? The use of an index can sometimes cause                 ?
?                 ? contention.                                             ?
+---------------------------------------------------------------------------+

 

Additional Information

Database Design - Adding Indexes