topic Re: Performance Multiple JOIN with DISTINCT in Application Development and Automation Discussions

Performance Multiple JOIN with DISTINCT

kuanchin — Mon, 27 Aug 2018 02:51:05 GMT

Dear ABAP master,

Good morning, currently our SAP system upgraded to :

NW FOR S4HANA NW 7.52 FOR S/4HANA 1709

SAP_ABAP 75C /SP2

For this below coding with DISTINCT and multiple JOIN, will it be consider BAD coding ?

Shall I change it with only JOIN, later use DELETE DUPLICATE ?

And JOIN with INNER + LEFT OUTER will also impact the performance ?

    SELECT DISTINCT a~matnr, a~mtart, a~matkl, a~meins , a~lvorm,
           b~werks, b~lgort, b~charg, ( ( b~clabs + b~ceinm ) ) AS quantity,
           c~vfdat, c~zustd, c~lwedt,
           d~maktx,
           e~stprs,
           g~waers,
           h~prgrp,
           h~werks
      FROM mara AS a
      INNER JOIN mchb AS b
         ON a~matnr = b~matnr
      INNER JOIN mch1 AS c
         ON c~matnr = b~matnr
        AND c~charg = b~charg
      LEFT OUTER JOIN makt AS d
         ON d~matnr = a~matnr
      INNER JOIN mbew AS e
         ON e~matnr = a~matnr
         AND e~bwkey = b~werks
      INNER JOIN t001k AS f
         ON f~bwkey = b~werks
      INNER JOIN t001 AS g
         ON g~bukrs = f~bukrs
      LEFT OUTER JOIN pgmi AS h
         ON b~matnr = h~nrmit
        AND b~werks = h~wemit
      WHERE a~matnr IN @it_s_matnr
        AND a~mtart IN @it_s_mtart
        AND a~matkl IN @it_s_matkl
        AND b~lgort IN @it_s_lgort
        AND b~werks EQ @i_werks
        AND c~vfdat LE @( sy-datum + i_days )
        AND c~vfdat NE @l_vfdat
        AND d~spras EQ @gc_en
      INTO TABLE @gt_data .

Kindly advice.

Very appreciate.

Best Regards,

Kuan.

Re: Performance Multiple JOIN with DISTINCT

matt — Mon, 27 Aug 2018 06:38:51 GMT

When you paste code, please use right-click "paste as plain text". In this way, you don't get the HTML tags pasted. I've edited it for you this time.

Re: Performance Multiple JOIN with DISTINCT

matt — Mon, 27 Aug 2018 06:45:01 GMT

As a general principle, making the database do the work is preferable to doing it in ABAP. So, as long as your query is producing the right results, it's fine.

However, you shouldn't use the AS structure. Like AS a and a~matnr. It is easier to understand and clearer which field comes from where if you omit the AS and use the name of the table directly. mara~matnr.

Re: Performance Multiple JOIN with DISTINCT

kuanchin — Mon, 27 Aug 2018 12:25:54 GMT

Dear Matthew,

Good morning, note with thanks.

Many thanks for your guidance.

Best Regards,

Kuan.

Re: Performance Multiple JOIN with DISTINCT

kuanchin — Mon, 27 Aug 2018 12:26:45 GMT

Dear Matthew,

Good morning, thanks for your sharing.

Appreciate.

Best Regards,

Kuan.