Optimizing SQL Statements: Parameters and Options
Article ID: 62802
Updated On: 11-30-2020
Products
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
Feedback
