ABAP programming standards for SELECT...ORDER BY....

[Former Member]

I recently saw a set of programming standards that stated it was better not to use an ORDER BY clause on a SELECT statement. Instead SELECT into an internal table and use SORT. The actual statement was....."In most cases it is preferable to do the sorting within the ABAP program instead of on the database server. That means: fill the internal table via a SELECT statement and then sort via the SORT statement instead of coding a SELECT u2026 ORDER BY. The sorting of large amounts of data on the database server affects the performance of all users on the system, whereas the sorting within the ABAP program u2018onlyu2019 affects the application server. However, if an index exists on the table that can be used for the sorting then the SELECT u2026 ORDER BY doesnu2019t cause any undue strains on the system."

I think this is no longer correct particularily with regards to Systemi/iSeries which we normally run in 2 tier mode.

What are people opinion on this?

[christian_bartels]

Peter,

the correct answer for DB2 on IBM i is "it depends". To implement "ORDER BY", the optimizer has basically these choices:

- If an index with the columns of the "ORDER BY" clause exists, the database can access the data through this index, which returns them in the desired order. However, the data needs to be fetched row by row, so for each returned row you need one I/O request.

- If such an index does not exist, the database can choose to perform a sequential read into a temporary storage and sort the temporary result set before returning the data to the caller. This is basically the same as reading the data into an internal table and let ABAP do the sort. Even if an index exist, reading the whole table into memory and sorting it there may be faster, because on a sequential read, the database can execute a "blocked" fetch rather than many individual I/O operations. You may see this if the table is relatively small.

- If such an index does not exist, the database could also choose to create a temporary index and access the data through the temporary index. For the first execution, you don't gain much, but subsequent executions may perform much better. However, after an IPL you loose all temporary indexes, so the index needs to be built again on the first execution after the IPL.

If you code your ABAP to read the table sequentially and sort it in memory, you are forcing the second implementation. If you code ABAP with the ORDER BY in the SELECT statement, you allow the database optimizer to choose the best possible implementation depending on the size of the table, the size of the result set, and the available indexes.

If your table is relatively large but the result set (based on the WHERE clause) is relatively small, make sure to have an index that combines the WHERE clause elements returning only one value with the ORDER BY clause elements.

For example, if you have a query like:

SELECT * FROM T

WHERE A = ? AND B = ? AND C IN (?, ?, ?)

ORDER BY D, E

you should have an index over columns A, B, D, and E for the best possible performance. A and B in the index can be used to limit the result set without any influence on the sort order D, E, but C cannot.

Kind regards,

Christian Bartels.

[lbreddemann]

Although I'm not a DB2 expert I'd say that this is outmost nonsense.

Databases are made to sort data.

They have the best options to perform this task in the most resource effective ways (e.g. using indexes).

By avoiding the ORDER BY clause you hide important information about your query from the database which may lead to very bad overall response times and resource consumptions.

If the database grinds to halt because of the sorting (thus affecting other users), you should rather check why this is happening instead of moving data handling code into your ABAP layer.

Besides that the DBMS supported by SAP do implement internal resource managers that should prevent single tasks from "taking over the database server".

regards,

Lars