Skip to Content
2

HANA performance issue - For All Entries IN with OR condition in WHERE clause

Jul 10, 2017 at 08:13 AM

89

avatar image

Dear Experts,

lately I had to optimize a program due to high memory usage and extremly big impact on the server performance (job didn't finish after 11h and has been killed because of 100% CPU consumption).

The ERP runs on HANA version: 1.00.122.05.148

The program had almost all performance killers like SELECT in LOOP, nested SELECT instead of JOIN, SELECT * etc.

What I didn't expect was very poor performance of SELECT statement like below:


SELECT a
  INTO TABLE tab_a
  FROM Z
  FOR ALL ENTRIES IN tab_b
  WHERE a = tab_b-a
   OR b = tab_b-b
   OR c = tab_b-c

For about 2500 rows in tab_b, the results come after about 20-40 seconds and database returned about 32000 rows (correct results).

I converted the SELECT statement into 3 single SELECTs and I get results immediately (as expected):

SELECT a
  INTO TABLE tab_a
  FROM Z
  FOR ALL ENTRIES IN tab_b
  WHERE a = tab_b-a

SELECT a
  APPENDING TABLE tab_a
  FROM Z
  FOR ALL ENTRIES IN tab_b
  WHERE b = tab_b-b

SELECT a
  APPENDING TABLE tab_a
  FROM Z
  FOR ALL ENTRIES IN tab_b
  WHERE c = tab_b-c

I know that I can get duplicate rows in case of rows when condition for several columns is true, but this is not a problem for me (perhaps using UNION would eliminate duplicates).

What's wrong with FAE with OR in WHERE clause?

Is it incorrect from logical point of view or I've found a HANA problem which has to be corrected?

Best regards

Rafal

P.S. the report now runs max 6min instead of 11h before :-)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Mike Pokraka Jul 10, 2017 at 08:27 AM
1

Interesting. My first thought is also that you may have stumbled on an optimisation glitch. If you can reproduce it in a simple form, i would report it to SAP.

FAE is generally not the most efficient as the criteria grow large, and it is usually more efficient to convert them into joins where possible. I haven't analysed FAE on HANA, but on a regular DB it will generate separate conditions for each row in your criteria table. So 1000 entries get converted to 1000 OR conditions in the DB's native SELECT statement. With complex conditions this can get quite messy.

Nevertheless, I wouldn't expect such a dramatic increase between the two techniques you describe. I would be curious as to the answer if you do report it.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

There is a note (sorry, don't recall the number), concerning FAE and HANA (for some reason, it's under BW, rather than ABAP). It suggest a database HINT to improve performance where there's no real alternative to FAE.

0