HANA 1.0 - Testing Selection speed

[roberto_vacca2]

Hi all,

I'd like to ask you an opinion.

I'm struggling with a matter. HANA performance Vs ORACLE performance on a Test system environment.

HANA and ORACLE have same package (Ehp8) and same machine power.

Only difference is that :

- SAP on ORACLE has Application Server and DB on the same server;

- SAP on HANA has Application Server on a machine and DB HANA on one other.

Those two does comunicate on optical fibre. Here in Test we still don't have HD ssd.

Here attached an image with time performance of HANA Vs ORACLE.

What I'm asking you is why these time differences.

Behind those data there's this simple SELECT:

First session of 100 Records - with one time read:

SELECT * FROM mara INTO @DATA(ls_mara).
  SELECT SINGLE * FROM makt INTO @DATA(ls_maktx)
        WHERE matnr = @ls_mara-matnr AND
        spras = @sy-langu AND
        matnr LIKE 'F%'.
  l_count = l_count + 1.
  CHECK l_count GT 100.
  EXIT.
ENDSELECT.

Second session with 10 records read 10 times:

SELECT matnr FROM ekpo INTO TABLE @DATA(lt_mat) UP TO 10 ROWS
      WHERE matnr LIKE 'F%'.


READ TABLE lt_mat INDEX 1 ASSIGNING FIELD-SYMBOL(<s>).
LOOP AT lt_mat ASSIGNING <s>.

  PERFORM select_val.

ENDLOOP.

FORM select_val.

  CLEAR l_count.
  SELECT * FROM mara INTO @DATA(ls_mara).
    SELECT SINGLE * FROM makt INTO @DATA(ls_maktx)
          WHERE matnr = @ls_mara-matnr AND
          spras = @sy-langu AND
          matnr LIKE 'F%'.

    l_count = l_count + 1.
    CHECK l_count GT 9.
    EXIT.

  ENDSELECT.

ENDFORM.

How can be possible those kind of differences?

And How can be possible that same select, divided 10 times, jump time till 4 times more??

Total of first and second session should be quite the same.

Sorry for the "cime di rapa" code 🙂

Thanks in advance

Regards

[Jelena_Perfiljeva · Accepted solution]

There are all kinds of performance analysis tools for ABAPers that can show you what exactly happens. I can't comment on Oracle vs. HANA results but assuming that both code samples would have the same runtime is incorrect, even before HANA.

Let's say you need to bring 100 cans of beer from a store. With option 1 you go to the store, get 100 cans and drag them home. With option 2 you go to the store, get 10 cans and bring them home. Then you go back to the store and bring 10 more, etc. Even though as a result you still end up with 100 cans of beer in both cases, the second process would be slower, especially if your store is far. This is a simplification, of course, but I hope you get the point.

[roberto_vacca2 · Accepted solution]

Hi Jelena. Thanks for your reply. I would really like better bringing beers than stay here calculating those times 🙂

Well, I've already assumed that the delay for communication between application server and db server would be the main problem but at the same time if you do the same kind of select in an oracle environment (DB and application on the same server), well you've almost the same total time for two options (a little difference ) and it seems obvious because 10 is equal to 1 * 10. In HANA you've x3/x4 times more of difference.

Furthermore, using your example, what I mean is that 100 cans of beer are heavy to bring and you'll be slower ( 2 km/h ) going from store to home than bringing 10 beers running at 10 km/h 🙂

By the way it seems that Mr Oracle is faster and with a better health than Mr HANA.

Are there any time benchmark of SAP HANA and Oracle 12 published somewhere?

Does Achille reach the tortoise or not? 🙂

Thanks for your support

Regards

[Jelena_Perfiljeva · Accepted solution]

Roberto, you posted this in primary tag SQL with cross-posting to ABAP and the only tag with HANA in the name is "SAP HANA rules framework" of all places. You are trying to compare HANA and Oracle with what seems like incomparable configuration (apples and oranges) using poorly-written ABAP statements and from there it seems you are drawing some general conclusions.

I'm sorry but this does not look like a serious question and I have no desire to participate further. You can find a lot of information in Google or at least by asking the right question in the right tag on SCN.

Good luck.

[roberto_vacca2 · Accepted solution]

Hi Jelena,

sorry If I seems to you not serious with my question. I really don't want to draw general conclusion. If I'd show you how much time I spent trying to understand why of this, it would takes a day of work, and you know we can't.

Furthermore, the code poorly written in ABAP was an example written by a long-experienced programmer of a consultant company (selling HANA), not me in first person, in a 2 hours test. Poor because of time. I could show you a code of 3000 rows, but I think it's not a good idea. I'm not against HANA. I want only to find a reason to promote HANA, but I find only walls. I would like to find a technical motivation , with clear benchmarks.

Tags are those that our Forum show us, and in a period where everything is tagged, I hope you can apologize this. I will target better next time, even if my question is cross-dependent and it's hard to find the perfect combination.

Thanks for your best wishes, I will search a good answer!

Regards

[Patrick_vN]

Hey Roberto,

Have you read this already?

And even though I'm curious to see exactly how much faster SAP HANA is, I have my reservations on the code examples you have used. SELECTS in LOOPS don't have a positive effect on performance, not on Oracle, not on SAP HANA. Nor does SELECT * for that matter.

What your example seems to prove is that bad code has worse results on SAP HANA. I'd be interested to see what the result would be if you use a JOIN statement, combined with aggregation and/or selecting only the fields that are needed. And all that for a million records..

[roberto_vacca2]

Hi Patrick, yes I've already read golden rules.

You know that every company has got its history and often the custom code that you could found is not always aligned with those golden rules, Furthermore you can't make a real change if you don't have people following you 🙂

The purpose of the example was exactly to test the worse results on HANA or ORACLE. I knew for example, that a "simple" select like this:

SELECT mtart, COUNT( * ) AS mcon, MAX( BRGEW ) AS mbrg, MAX( VOLUM ) AS mvol FROM mara WHERE matnr LIKE 'F%' GROUP BY MTART INTO TABLE @DATA(lt_tab)<br>

is faster in HANA than Oracle old system.

But if you put this Logic on Oracle DB with store procedure, you could get the same results.

To reply to you, with a million record and all joins, it will have probably the same time of Oracle So the question was initially if there was something wrong in the architecture between Application and DB server machines.

I've finally reached the conclusion that there's nothing wrong and the code, in some cases, will be probably re-enginereed.

I think with HANA we're not living a revolution but a feeling of revolution 🙂

What do you think?

Thanks, regards.

[matt]

If the tables are column store in HANA, then you'll get not very good results with select *. Also 100 records seems a very low number to do performance testing with.

You should use the runtime analysis tool to get a breakdown of what's going in your program - where the bottlenecks might be, how much times spent on the database etc.

[roberto_vacca2]

Hi Matthew.

I've already used ST12 and other tools on custom reports. This is only an example to get the impact of a select instruction.

There's too much difference in my opinion between Oracle DB and Application server on the same machine and HANA DB with Application server on different machines.

This could be caused by an incorrect configuration or it's a HANA "feature"? Who knows.

Changing from rows to column doesn't make any real difference with a comparison in oracle.

With a row configuration In HANA i get a better performance with SELECT * and it's a "true lie", but there's still difference with Oracle.

Now my question is: are these the same times you found in your HANA systems with these instructions? If true then probably we miss something in our configuration system/architecture and I would like to know your architecture.

If not, I suppose it's an HANA feature.

I would like to say that even simple CDS and DB Procedure, already tested, gives lower performance than Oracle.

Anyway I like the beauty of CDS and DB Procedure, more machine oriented. 🙂

Thanks

Regards

[roberto_vacca2]

Probably I found the answer. It's a real HANA feature.

Aggregation and a correct "TAG" , now is the new "religion". With the "group by" feature in a select instruction, and with a perfect selection of what users's mind really want, we've better performance, quite the same of Oracle.

We need only to concentrate all the logic in a single simple and readable, enormous fat query for a profile of a functional experienced, and do a reengineering of all custom developments if we want to reach advantages with HANA as in Oracle before.

Finally ABAP is SQCOBOL oriented! That's a true red revolution! 😄

Someone's got any ideas to do all this in a small budget environment, in a country with a poor economy trend? I suppose that Trump has got a Real solution for a low budget 😄

Thanks all, regards.

[nomssi]

Hello Roberto,

your code is doing SELECT SINGLE (MAKT) in a LOOP (MARA), there is a data transfer for each MATNR found. The data transfer time between the application server and the database has a major impact on performance. In this case, it seems obvious to me the database server that has the best network connectivity to the application server wins.

By using a single SELECT statement with LEFT OUTER JOIN, UP TO n ROWS you could greatly reduce the number of data transfer from the database (Code Push Down).

I have been wrong before, so do your own measurements (SQL Trace - Transaction ST05 / Start Trace / Stop Trace / Extended Trace List / DB Explain).

JNN

[roberto_vacca2]

Hi Jacques,

thanks for reply.

As I wrote before, my question is not about the better performance I can get with these instructions I wrote. My question is to know why so many differences between ORACLE-SAP Architecture and HANA-SAP architecture.

I would like that you test those instructions in your HANA system and tell me if you get the same times or to give me a trace.

Thanks for your attention.

Regards.