Who says HANA doesn't work fast for OLTP?

[Former Member]

Sometimes people think that because HANA is a columnar database, it doesn't run fast for simple OLTP operations. I was just looking at a performance problem with class /IWBEP/CL_MGW_ABS_MODEL, method GET_LAST_MODIFIED.

This had some screwy ABAP, which is incidentally fixed in SAP Note 2023100 (thanks oliver.rogers for finding the note), and it generated the following SQL:

SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c

FROM "REPOSRC"

WHERE "PROGNAME" LIKE 'CL_ABAP_COMP_CLASS%' AND "R3STATE" = 'A'

ORDER BY "CDAT" ,"UDAT" ,"UTIME"

That SQL is pretty nasty, because it does a wildcard search on a big table. On the non-HANA system it was running in 20 seconds. I did the root cause analysis in the database and found that it was searching the primary clustered index, which was 98% fragmented.

Obviously I rebuilt the index - these are the results.

CPU time = 2750 ms,  elapsed time = 2746 ms.

CPU time = 2594 ms,  elapsed time = 2605 ms.

CPU time = 2750 ms,  elapsed time = 2764 ms.

I realized at this point this was some bad coding, so I found the fix thanks to Oli and we put the change in. That fixed the performance problem.

But then I thought... what happens if you run this bad query on a HANA system? This is just what custom code looks like a lot of the time...

Statement 'SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c FROM "REPOSRC" WHERE "PROGNAME" LIKE ...'

successfully executed in 12 ms 414 µs  (server processing time: 11 ms 613 µs)

Fetched 8 row(s) in 0 ms 68 µs (server processing time: 0 ms 0 µs)

Statement 'SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c FROM "REPOSRC" WHERE "PROGNAME" LIKE ...'

successfully executed in 9 ms 778 µs  (server processing time: 9 ms 136 µs)

Fetched 8 row(s) in 0 ms 64 µs (server processing time: 0 ms 0 µs)

Statement 'SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c FROM "REPOSRC" WHERE "PROGNAME" LIKE ...'

successfully executed in 12 ms 677 µs  (server processing time: 11 ms 830 µs)

Fetched 8 row(s) in 0 ms 56 µs (server processing time: 0 ms 0 µs)

So anyDB is averaging 2705ms, and HANA is averaging 10.86ms, an average speedup of 249x.

You may be saying... OK well that's for poorly written SQL - what about when it was optimized. Sure, let's test in that scenario. Here's the SQL:

SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c

FROM "REPOSRC"

WHERE

"PROGNAME" IN ('CL_ABAP_COMP_CLASS============CCDEF', 'CL_ABAP_COMP_CLASS============CCIMP', 'CL_ABAP_COMP_CLASS============CCMAC', 'CL_ABAP_COMP_CLASS============CI', 'CL_ABAP_COMP_CLASS============CO', 'CL_ABAP_COMP_CLASS============CP', 'CL_ABAP_COMP_CLASS============CT', 'CL_ABAP_COMP_CLASS============CU')

AND "R3STATE" = 'A'

ORDER BY "CDAT", "UDAT", "UTIME"

So ran it on anyDB, I couldn't get accurate results from the SQL console so I had to use the ABAP trace to get the numbers. They were 5.504ms, 1.484ms, 4.605ms for an average of 3.86ms. Let's see how HANA compares.

Statement 'SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c FROM "REPOSRC" WHERE "PROGNAME" IN ...'

successfully executed in 1 ms 977 µs  (server processing time: 1 ms 156 µs)

Fetched 8 row(s) in 0 ms 63 µs (server processing time: 0 ms 0 µs)

Statement 'SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c FROM "REPOSRC" WHERE "PROGNAME" IN ...'

successfully executed in 1 ms 946 µs  (server processing time: 1 ms 250 µs)

Fetched 8 row(s) in 0 ms 60 µs (server processing time: 0 ms 0 µs)

Statement 'SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c FROM "REPOSRC" WHERE "PROGNAME" IN ...'

successfully executed in 2 ms 230 µs  (server processing time: 1 ms 127 µs)

Fetched 8 row(s) in 0 ms 59 µs (server processing time: 0 ms 0 µs)

With HANA then, we get an average of 1.18ms for an average speedup of 3.27x.

Conclusions

For poorly constructed OLTP queries at the database level, we can get enormous benefits of running HANA - up to 250x or more. With optimized SQL that hits database indexes on anyDB, that drops to around 3.27x, but SAP have only ever claimed a 2-3x increase of running ERP on HANA for transactional workloads.

And remember if you move to the sERP suite, you'll see another 2-3x because the data structures are simpler. That's going to mean response times of 5-10x faster than on anyDB.

I don't know about, you, but that feels significant to me.

Yes, I know I didn't do concurrency, inserts, updates and all that jazz. This was just a quick test run with 15 minutes of spare time. Hope it is informative. It's also worth noting that with program changes, I was in this case able to get acceptable performance using anyDB for our timesheet app. The only area where performance is a problem is for the WBS element search, which is a wildcard search again.

For those searches, HANA rocks. For customer, product - anything with a free text search, HANA is going to kill anyDB.

P.S. This was all run on HANA Rev.81

P.P.S Why not run this on your DB and let us know how it runs?