How to integrate SQL Server specific hints in ABAP

Working on a few Proof of Concepts this year where customers moved their SAP landscapes from Oracle or DB2 to SQL Server, we sometimes encountered the request by customers to take their ABAP Open SQL hints for DB2 or Oracle over and convert them to SQL Server syntax. In the course of those adaptions, a lot of questions usually come up about what can be hinted and how is it exactly done. Therefore let’s go through a few of the usual obstacles.

To begin with Note 129385 - Database hints in Open SQL and Note 133381 - Database-Hints in Open SQL for MS SQL Server should be studied.

A word before we start with the deeper look into this. It is the ABAP developer who is responsible for correctly hinting and making sure that the hints make sense. We will see that SQL Server offers query or table hints which are contradictory, but can be added to an ABAP SQL statement and as such would be added to the resulting SQL statement. Sometimes even situations could occur where SQL Server would issue an error since the ABAP hint combinations might prevent generation of a query plan. We also don’t want to discuss what hints make sense and which do not. In this article we just want to explain the mechanics of adding different hints for SQL Server to ABAP code.

Especially for cases where customers move from Oracle to SQL Server, there is one very significant difference. If an index hint is added to an Oracle query specifying an index that does not exist, Oracle will still execute the statement and will pick one of the existing indexes. In SQL Server an index hint specifying an index that does not existent will result in a query error and no results will be returned.

First of all let’s look on what SAP offers in their database interface as hints and how we specify that such a hint should address SQL Server.

 

Index Hints interpreted by the SAP Database Interface

Addressing SQL Server with a hint, a qualifier like this needs to be appended to the ABAP Open SQL Statement in ABAP: %_HINTS MSSQLNT

After having done that one now can specify the hints the SAP Database Interface will take and adapt to the correct syntax for SQL Server. First and most common hint method is to direct the SQL Server Cost Based Query optimizer to use a specific index.

First thing to specify is the table name. There we have two possibilities. Either naming the name of the table directly or using a kind of placeholder which puts the table name into it automatically. Naming the table directly by name we usually use when the hint is used in a join between multiple tables. The latter one of just putting a placeholder in can be used when a statement just references one table or when we look at a dynamic Open SQL Statement where the table at coding time simply is not exactly known and could be several different tables. Taking the order header table VBAK as an example, the hint syntax could look (though not complete yet) like:

%_HINTS MSSQLNT ‘TABLE VBAK ‘

Or

%_HINTS MSSQLNT ‘TABLE &TABLE& ‘

First possibility named the table by name (VBAK). The second syntax we put a placeholder in for the table. Now we need to specify the index for the table. The SAP DBI helps in this case, especially for the case where the coding done could run on SAP systems which might have had very different life times already. In the last 20 years, SAP changed the naming convention around tables and indexes several times. Hence it is not sure that the index which happens to have the extension ‘~ABC’ has the very same extension on a system which already got installed 15 years ago. The index actually could have the extension ‘___ABC’ if the system got installed ages ago. Hence one can specify only the 1-3 letter/digits of the index extension (like ‘ABC’) and go ahead. The hint could look like:

%_HINTS MSSQLNT ‘TABLE VBAK ABINDEX(ABC)‘

Or

 

%_HINTS MSSQLNT ‘TABLE &TABLE& ABINDEX(ABC)‘

Now it is to the SAP DBI interface to figure out what the correct name of the index with the extension ABC is on the table VBAK. Please be aware that within the hint index names, table names and index extensions need to be written in the same way as those exist on the database. Since table name, index names and index extensions usually are upper case on the database, those need to be written uppercase within the hints text as well (in opposite to the rest of the ABAP SQL statement where upper and lower case writing doesn’t matter.

The real Open SQL ABAP statement would look like:

The result statement executed against SQL Server would look like:

Another four hints are supported by SAP DBI of which the reparse hint probably is the most well-known. The usage of it would look like:

%_HINTS MSSQLNT '&REPARSE&'.

This hint basically would add ‘OPTION (RECOMPILE)’ to the SQL statement. As a result with every execution the statement is getting recompiled by SQL Server. The other 3 hints will be discussed closer in some other BLOG articles.

 

How to use the normal hints SQL Server has to offer?

When you look into this piece of SQL Server documentation: https://msdn.microsoft.com/en-us/library/ms181714.aspx, you realize that there are many more hints SQL Server supports and that the hints are divided into so called query hints which apply to query as a whole independent of how many tables are referenced and table hints which only apply to the table the hint(s) is referenced with. Hence the first type of hints usually shows up at the end of the SQL statement defined by the keyword OPTION. Whereas the table hints are listed directly behind the table name in the table list of the statement using the keyword ‘WITH’. The great thing about the implementation of the SAP ABAP stack on SQL Server is that it does allow leveraging all these hints in ABAP Open SQL Statements.

 

SQL Server table hints in ABAP

If we want to specify one of the SQL Server table hints, the syntax in ABAP is more or less the same as explained for the index hint above. Means we have a second possibility to define an index hint. Such an index hint could look like:

%_HINTS MSSQLNT 'TABLE VBAK INDEX([VBAK~0])'

Or

%_HINTS MSSQLNT 'TABLE VBAK INDEX =[VBAK~0]'

Very important to note. In this case the index name must be written exactly in the way how its spelling is in the database. It also is mandatory to have the index name in the quotes or brackets ( [ ] ) as shown above. Otherwise a syntax error is created when executing the statement on SQL Server.

One also can combine these table hints like in this example:

%_HINTS MSSQLNT 'TABLE &TABLE& INDEX([VBAK~0]), FORCESEEK'

But don’t forget the comma separation. If the comma is missed, just the first hint might be taken as hint into the SQL statement.

 

SQL Server Query options in ABAP

Now we are talking about the hints which can be used to affect the behavior of the query as a whole. These hints can be used in an ABAP Open SQL statement as well. Let’s assume there is one special query which should be enabled to be executed in a higher degree of parallelism than the instance wide SQL Server setting is allowing. In such a case the hint in the ABAP statement would look like:

%_HINTS MSSQLNT 'OPTION MAXDOP 16'

Very important is not to use the keyword ‘TABLE’ in the hint, but use ‘OPTION’ instead. The terms behind the keyword OPTION will be taken and as such added into the option clause into the SQL statement

Combining such query hints is possible. Let’s assume we want to combine the hint above with a force order hint. In such a case the hint would look like:

%_HINTS MSSQLNT 'OPTION MAXDOP 4, FORCE ORDER'

Combining two hints like this, it is essential to separate those by a comma. Otherwise, the SQL statement will fail with incorrect syntax. But in this way, separating several query hints by a comma, one can combine them.

One can also combine a table hint with a query hint. Let’s assume we want to direct the query to a specific index and also want to enable a higher degree of parallelism for one specific query. The resulting index hint could look like:

%_HINTS MSSQLNT 'TABLE &TABLE& ABINDEX(0)'
              MSSQLNT 'OPTION MAXDOP 16'

Or looking at the whole ABAP statement, we would look at something like:

The resulting SQL Query would look like:

As you can see both types of hints got added to the query.

 

Special case of handling queries which join between tables

If we want to apply table hints for queries which join between two or multiple tables, one needs to specify each table exactly, like in the case below which covers a specific customer case.

In this case the SAP DBI notion was taken to direct using the index ‘~M’ for the MSEG table and using the index ‘~0’ for the MKPF table. Looking at the SQL Statement generated out of the ABAP Open SQL query, we would look at:

This should give a good idea on how one can leverage the methods the SAP Database Interface offers to use and hints and how one can use all the table and query hints SQL Server uses out of ABAP coding. Hope this supplements a bit the two OSS notes I mentioned at the beginning.

 

Conclusions

One can use all the index hints which are exposed by SQL Server in ABAP as well. However there is additional responsibility for the programmer using such hints to make sure that these indexes exist and remain in the database. The person creating the hint also needs to make sure that the hints do make sense and are appropriate, especially when hints are given to the query optimizer to behave in certain way. Changing data characteristics in a table can make a hint obsolete and result in a less optimal behavior of a query.

There are two different ways to hint a certain index. However in both cases it is important to name the table, index name and/or index extension exactly as it is known to the database. As SAP uses a case sensitive database collation names written in lower or uppercase are different. Means ‘VBAK~001’ is evaluated different than ‘vbak~001’. Therefore it is important to spell the names correctly, especially in regards to lower and upper case.

Comments

• Anonymous
  August 31, 2011
  Great detail here!

• Anonymous
  April 20, 2015
  Dear Juergen! Thanks for sharing!

• Anonymous
  April 14, 2016
  i want to know the difference between this two syntax %_HINTS MSSQLNT 'TABLE EVER ABINDEX(VKO)'.and %_HINTS ORACLE 'INDEX("DFKKRH" "DFKKRH~002")'.

• Anonymous
  July 28, 2016
  The comment has been removed

  • Anonymous
    July 29, 2016
    The comment has been removed