Skip to Content
author's profile photo Andreas Spix
Andreas Spix

ORDER BY in Custom-Code for HANA

Posted on Jul 20, 2016 at 09:46 AM 2.2k Views

Hi,

we are currently checking our custom-code and the most painful finding is the missig ORDER BY Statement. Before we checked our code we've searched the internet for hints to adjust the abap-code for hana. But in our findigs there were no explicit hints to the ORER BY. The Document "Condiderations for Custom ABAP Code During Migration to SAP HANA" describes the implicit sorting but there is no REDLIGHT for that.

In my opinion this is the most frequently finding in custom-code!

So I have a question for all custom-coders: ow shall we deal with the ORDER BY? Adjust all findings (we have more than 2500 findings!!!)? Adjust only special findings - but what ist "special"? Or no adjustments an we will see what happens in our functional tests?

Regarding for your answers. Thx.

Andi

ABAP Development
Add comment
10|10000 characters needed characters exceeded

2 Answers

  • author's profile photo Joachim Rees
    Joachim Rees
    Posted on Jul 20, 2016 at 10:18 AM

    If you have assumed in the past that the lines returned by a select are always sorted in a specific way (which they are not) and relied on this assumption (what should not have been done), the yes, I think you should adjust all those findings with the appropriate ORDER BY to assure the results will be sorted the way it's expected by the following coding.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Edo von Glan
    Edo von Glan
    Posted on Jul 21, 2016 at 10:37 AM

    Hi Andreas,

    my understanding is as follows:

    • Even without HANA, it is not guaranteed in what order the records are returned (without ORDER BY)
    • BUT usually this does not lead to problems, because in most cases non-HANA databases return the records in the sort order of the primary key. For Oracle databases, SAP says in note 825653, Nr. 9, that the sort order depends on the key that is chosen for access by Oracle's cost-based optimizer.
    • (Thinking more about it, problems will arise if there is an SQL command that used to use the primary key when the code was originally written and tested, and then the Cost-Based Optimizer decides differently after an Oracle upgrade, migration to new hardware, or things like that. We had a few problems resulting from this after migrating to different hardware.)
    • Because with HANA, data is stored per column, I assume there is no such thing as a primary key. Therefore, I think with HANA it is very probable that every missing ORDER BY (that is not a false positive) will lead to different behavior of the program.
    • I think it is less effort to clean up the coding, than to think through for each case whether it is a false positive (whether the final result/output of the program will be incorrect due to different sort order).

    Best regards,

    Edo

    Add comment
    10|10000 characters needed characters exceeded

    • Lars Breddemann Edo von Glan
      Jul 22, 2016 at 08:15 AM

      Sorry mate, surely wasn't meant as bashing in your direction. Not at all!

      I see and partly agree with the point of educating people.

      Yes, there are a lot of rather crappy defaults and degrees of freedom (permanent SELECT * ? rubbish! This should automatically extend to the full column list or a selection dialog or... , SELECT without ORDER BY followed by loop constructs? Put the ORDER BY there automatically and take the error rather on too much sorting, which is easy to fix if really avoidable, etc.).

      On the other hand is the fact that ABAP developers typically are paid for their work and have quite an influence on the way their customers work. I think it's fair to expect careful work from them.

      cheers,

      Lars