Optimizing SQL Statements: Parameters and Options
search cancel

Optimizing SQL Statements: Parameters and Options

book

Article ID: 62802

calendar_today

Updated On:

Products

IDMS IDMS - Database IDMS - ADS

Issue/Introduction

Optimizing SQL Statements: Parameters and Options

This is a review of the some parameters that can be coded in SQL statements, which tend to create more costly (or slower) access plans. It also reviews what options to use to improve SQL Query performance.

 

Environment

All supported releases of IDMS

Resolution

Sometimes a logical SQL request can be coded multiple ways, yet return the same results. In considering how to code your SQL statement, remember that some parameters may result in resource-intensive access paths. These parameters include PRESERVE, ORDER BY, LIKE, and OR.

Queries using these parameters may need to do an area sweep to return all result rows that meet the selection criteria; this can create problems in a large production database. Before moving an SQL application to production, it's important to use the EXPLAIN statement to see the access path for all SQL statements.

If it is not possible to develop an alternative syntax that performs more efficiently (such as by accessing an index or calckey), then using a table procedure or procedure should be considered.

 

Additional Information

CA IDMS 19.0 - Administering - SQL Statements - Access Module Management Statements