Impact of embedded functions on query optimization

Document ID : KB000018765
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Query optimization is important. Use of functions in the query (such as CAST, DIGITS, TRIM, etc) can have an impact on that optimization and thus on the query performance.

Solution:

Many factors can impact how an SQL query will optimize, especially if the query is against a network database. If a query is not performing as hoped, it is important to use the EXPLAIN command to show how a query is optimizing, If indexes are available on the database but the query is not using them to access the data, one reason for that can be the use of functions in the SQL syntax. For example., if the query contains a clause like .. CAST(EMP_ID AS CHAR(5)), then the optimizer will not be able to able to use an index where EMP_ID is the high-level sortkey, because the query is transforming the original sortkey field into a different format.

In an instance like this, it is important to review the syntax and consider if there are viable options. For example, consider a situation where two records contain the same data but in different formats: EMP_ID is defined as NUMBER(5,0), and MNGR_ID is defined as CHAR(5). If you want to compare these, then you might be using a query with a WHERE clause such as

WHERE CAST(EMP_ID AS CHAR(5)) = MNGR_ID ..

This query would successfully compare the values and select the matching rows, but it might perform poorly because it would not be able to use an index defined on EMP_ID to retrieve that record. Changing the clause to read

WHERE EMP_ID = CAST(MNGR_ID AS NUM(5,0))

would accomplish the same result, but would allow us to use an index based on EMP_ID to retrieve that record.

Situations like this may occur where character data and zoned numeric data are used interchangeably in a network database. COBOL allows you to manage this easily, but SQL is not as flexible. Because SQL requires the data types of two elements to be the same before they can be compared, some use of scalar functions is a great way to enable those comparisons. It is important to evaluate the different ways they can be inserted into a query in order to ensure the most efficient performance of the query.