SAP HANA - Inner Join Performance Optimization

[#1]

Hi,

I have below sql statement. I would like to see if I can optimize the performance of it, as it is currently taking around 7 minutes to execute.

Any ideas on how I can improve the performance?

/BIC/ANSD300000 contains around      189.5 mio records

/BI0/MCUST_SALES contains around      1.7 mio records

/BIC/ANSD260000 contains around           7.5 mio records

SELECT s."/BIC/CUSHIER6", b."MATERIAL", b."BILL_DATE", b."NETVAL_INV" * b."BILL_QTY" as "Lastest NIP"

FROM

            ( ( "/BIC/ANSD300000" as b inner join "/BI0/MCUST_SALES" as s on                      

                         b."SOLD_TO" = s."CUST_SALES" and

                         b."DISTR_CHAN" = s."DISTR_CHAN" and

                         b."DIVISION" = s."DIVISION" and

                         b."SALESORG" = s."SALESORG" )

                         inner join "/BIC/ANSD260000" as o on

                                     b."DOC_NUMBER" = o."DOC_NUMBER" )

WHERE

            o."DOC_TYPE" = 'ZOR' and

            b."BILL_TYPE" = 'ZF2' and           

            s."DATETO" = '99991231' and

            b."DISTR_CHAN" = '01'

ORDER BY s."/BIC/CUSHIER6", b."MATERIAL", b."BILL_DATE" desc;

Thanks in advance.

KR

Torben

[former_member185511]

try using CE functions,

i explained little bit here

based on my tests, run time is nearly halved. (2 times better performance)

http://www.bilencekic.com/2015/10/07/performance-sap-hana-ce-functions-vs-select-statement/

[lbreddemann]

The official recommendation is not to use CE functions anymore.

They are way more difficult to write, provide no better semantic, and prevent SQL optimization which is especially useful for cases like the presented one.

[former_member185511]

yea it is hard to write also i get some "column store error" during selection. better not to use but in some of my cases i am using (expecially cost and profit allocation) is very fast i am happy with the result

[SergioG_TX]

Torben, if you are able to do this in a graphical calc view, I think the Calc engine should be able to help with the optimization, otherwise, in plain sqlScript you may want to break down the complex query into multiple simpler sub queries to take advantage of parallel processing. Keep in mind to filter first and later aggregate (if any is needed), for example: q1 = select .. from table_or_view where condition = value(s); q2 = select .. from table2_or_view2 where condition = value(s); . . output = select (columns) // column(s) from table(s)         from :q1, :q2  // do any joins         where additional_condition = additional_values not a straight answer but better than just saying look at your plan analyzer 🙂 one thing to know though is : what is your goal?  execution time, number of records, etc?

[lbreddemann]

Without seeing the PlanViz, the explain plan, the table definition, SAP HANA revision.. etc. it's going to be a guessing game of where time is spend why.

Also: how many records are returned?

Looking at the selection criteria I'd guess an awful lot..

[Former Member]

Hi Lars & Sergio,

first of all thanks for your input - secondly, sorry about my late response.

Requirements has developer a bit since the sql statement i pasted above - as well have an implementation been done - I'm however not satisfied by the performance.

The raw SQL is as follows:

SELECT b."BILL_NUM", b."BILL_ITEM", b."BILL_DATE", b."DOC_CURRCY", b."MATERIAL", t."/BIC/TRADE_ID", c."/BIC/CUSHIER6", c."CUST_SALES", b."NETVAL_INV", b."BILL_QTY", c."DATEFROM", c."DATETO"

FROM "/BIC/ANSD300000" AS b

INNER JOIN "/BI0/MCUST_SALES" AS c ON b."SOLD_TO" = c."CUST_SALES"

INNER JOIN "/BIC/MTRADE_ID" AS t ON c."/BIC/CUSHIER6" = t."/BIC/CUSHIER6"

INNER JOIN "/BIC/ANSD260000" as o on b."DOC_NUMBER" = o."DOC_NUMBER"

WHERE b."BILL_TYPE" = 'ZF2' AND 0."DOC_TYPE" = 'ZOR';

However it is implented and used in ABAP - in a for all entries statement:

  IF gi_material IS NOT INITIAL.
    SELECT b~bill_num b~bill_item b~bill_date b~doc_currcy
           b~material
           t~/bic/trade_id c~/bic/cushier6 c~cust_sales
           b~netval_inv b~bill_qty c~datefrom c~dateto
      INTO CORRESPONDING FIELDS OF TABLE lt_temp_table
            FROM /bic/ansd300000 AS b
      INNER JOIN /bi0/mcust_sales AS c
      ON b~sold_to EQ c~cust_sales
      INNER JOIN /bic/mtrade_id AS t
      ON c~/bic/cushier6 = t~/bic/cushier6
      INNER JOIN /bic/ansd260000 as o
      on b~doc_number EQ o~doc_number
      FOR ALL ENTRIES IN gi_material
      WHERE t~/bic/trade_id EQ gi_material-tradeid AND
            b~bill_type EQ 'ZF2' AND
            o~doc_type EQ 'ZOR' AND
            b~material EQ gi_material-material AND
            c~dateto GE gi_material-date1 AND
            b~bill_date LE gi_material-date1.
  ENDIF.

for 25000 records in gi_material the execution time for the for all entries statement is 3-4 min. Over time that will increase if nothing is done as we get more and more data into those tables.

I would like to minimize the execution time as the program is running every 5 min.

The HANA revision in use is 85.02. It is on a scale-out platform running BW on HANA, hence the big DSO tables (/BIC/A*00) are partitioned using HASH partitioning (hence standard partitioning in BW on HANA).

The explain plan for the raw SQL is as follows:

The virtualized plan (prepared) looks as follows:

Details for the high cost part:

Any idea on how I can optimize? (create the stuff in views instead and consume that given view as a DDIC object in ABAP? utilize CDS views? etc)

Thanks in advance. KR

Torben

[lbreddemann]

The prepared plan doesn't tell us where the time actually is spent. It's really just the graphical version of the explain plan.

Based on the numbers we see there, the result set that gets created is in the 700 millions, which clearly leads to the question: what do you want to do with the data?

Running this query every 5 minutes... what for?

The assumption that the more records you join the longer it might take is not wrong, but assuming a linear or worse growth of runtime with the growth of data is not appropriate.

Another important piece of the total query performance is that you are running in a scale-out scenario. Here table distribution can be critical as intra-node data transfer can take a long time.

The executed planviz will also shed light on this.

Finally, your SAP HANA version is ancient. SPS 11 is the current one and with SPS 8 you're missing out on a lot of optimizations that have been implemented since then.

[Former Member]

Hi Lars,

basically we get a lot of external data into the system. We load these data every 5 min and in peak periods we need to be able to handle 25.000 received records within the 5 min slot.

The external data contains some key component that I use in the SQL the where clause, namely a so called Trade ID (basically a sort of customer number), a material number and a controlling date.

Now for each of these records (based on trade id, material and controlling date) I need to find the latest billing item available (need to perform some joins to get there in order to get only based on a certain sales order type etc.).

But basically, if could use below statement for each of the external data records I receive - however that will be 25.000 calls toward the HANA every 5 min and I guess that is insufficient as well (ok, my current approach seems to be insufficient as well).

As you see it is TOP 1 selection (I only need to first record returned - there might actually be many records but I do only need the first one - I know I only need the first one due to the order by clause). I have high lighted the controlling parts, namely the trade id, the material and a controlling date that is to be used to find the billing data I need to get for a external record received.

I dont know if there is a smarter way to do it than what I do today - the problem with my current SQL (shown below - I pass on 25.000 records at a time in a for all entries statement) is that it returns WAY more records than I need. I basically just need the first record for each Trade ID, Material, Controlling date conbination I prove in the gi_material internal table.

  IF gi_material IS NOT INITIAL.
    SELECT b~bill_num b~bill_item b~bill_date b~doc_currcy
           b~material
           t~/bic/trade_id c~/bic/cushier6 c~cust_sales
           b~netval_inv b~bill_qty c~datefrom c~dateto
      INTO CORRESPONDING FIELDS OF TABLE lt_temp_table
            FROM /bic/ansd300000 AS b
      INNER JOIN /bi0/mcust_sales AS c
      ON b~sold_to EQ c~cust_sales
      INNER JOIN /bic/mtrade_id AS t
      ON c~/bic/cushier6 = t~/bic/cushier6
      INNER JOIN /bic/ansd260000 as o
      on b~doc_number EQ o~doc_number
      FOR ALL ENTRIES IN gi_material
      WHERE t~/bic/trade_id EQ gi_material-tradeid AND
            b~bill_type EQ 'ZF2' AND
            o~doc_type EQ 'ZOR' AND
            b~material EQ gi_material-material AND
            c~dateto GE gi_material-date1 AND
            b~bill_date LE gi_material-date1.
  ENDIF.

[lbreddemann]

I'm not familiar with your data model, but what I understand is that you have a list of material numbers and for each one of those you want to retrieve the most current billing item.

From your statement that by ordering the data by the BILL_DATE and DATE_TO (both in descending order) and taking on the first result record, you already specified that you're interesting in the maximum values for both items.

So why not have a sub query, that computes these MAX values and apply the result as a filter?

Also: why would you want to use an internal table here? Why the detour through ABAP? Don't you have the list of new items on DB level?