How to improve the performance in SELECT Queries?

Improve the performance by adding hints in SELECT clause

If there are many indexes that contain common fields (or for any other reason), the database
optimizer cannot decide the right index to be used for a particular query.


Hints can be provided using the %_HINTS parameter. We will see the syntax for specifying
HINTS within your SELECT clause in order for a particular index to be used by the database
optimizer. We will see how the hints may be specified when the underlying database is
MS SQL Server.

We will have a small program that runs a SELECT statement on the table
ZST9_VBAK. We will use the index (Z12) that we created in the previous Creating Secondary
Indexes in Database Tables recipe.


For creating the program containing the SELECT clause with the HINT parameter, proceed
as follows:
1. A parameter P_AUFNR is declared for taking as input an order number.
2. Next, a data variable myvbeln is defined.
3. A SELECT statement is then written. The addition %_HINTS followed by the database
name, the table, and table index name is made to the SELECT clause.

It is a very simple addition. There is a special syntax used for specifying the name of the
database index to be used for the particular SELECT statement. The name of the index we
used can be taken from the index name as defined in the ABAP Dictionary. The table name
and the index name are specified within TABLE <tablename> ABINDEX(<suffix>). The syntax
should be proper because other than the database name check, the syntax checker does not
check the index name. Therefore, if a wrong index is used, the corresponding query will not
give a syntax error but the desired results will not be achieved.

In our case, we use MSSQLNT as the database name. However, you may use other database
names such as ADABAS, AS400, DB2, DB6, INFORMIX, MSSQLNT, and ORACLE (depending on
what is applicable to your underlying database). The code pertaining to the HINT parameter of
one database may differ from that of another.
For example, if we have an ORACLE database, the same Z12 index may be specified in the
SELECT statement in the following manner:

Note the database name added along with the changed format for index specification.