topic Re: Performance issue with a select statement using 5 joins.......Help!! in Application Development and Automation Discussions

Performance issue with a select statement using 5 joins.......Help!!

Former Member — Tue, 16 Oct 2007 08:10:03 GMT

Hi everyone,

I am using one select statment in my code which is having 5 joins. This piece of code had got performance issue. i know that SAP recommends maximum of 3 joins in a select statement, so i want to restructure this piece of code and check. Expect if anyone of you can suggest me efficient way of doing that.

Waiting for a prompt reply.

Thanks,

Rupesh

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Tue, 16 Oct 2007 08:17:52 GMT

can you give the select query that you have used ?

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Tue, 16 Oct 2007 08:19:43 GMT

Hi Rupesh,

Use For All entries in conjunction with Inner Join....

this should improve the performance...

eg:

SELECT T5~MBLNR

T5~MJAHR

T6~MATNR

T6~MENGE

INTO TABLE I_MSEG

FROM MKPF AS T5 INNER JOIN MSEG AS T6

ON T5_{MBLNR = T6}MBLNR AND

T5_{MJAHR = T6}MJAHR FOR ALL ENTRIES IN I_ITAB1

WHERE T5~BUDAT IN S_START AND

T6~MATNR = I_ITAB1-MATNR AND

T6~BWART = C_BWART AND

T6~AUFNR = I_ITAB1-AUFNR.

Reward points if this helps.

Manish

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Tue, 16 Oct 2007 08:31:41 GMT

JOINS : if you use join for more than 2 tables then it will give you bad performance

you know what happends if you use joins on tables

the data base connectivity exists up to the program execution that means load on the data base will be high , thats why it will result in bad performance

FOR ALL ENTRIES : use for all entries for ur query, with this also you can get output from more than 2 tables also

what happens if you use joins you know , the 1st select query will get the data from data base in one shot . there won't be any connection with data base so that it result in good performance

all ways don't use JOINS if you have more than 2 tables . use all ways FOR ALL ENTRIES

Reward if usefull

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Wed, 17 Oct 2007 00:07:52 GMT

Please post your code. Without that we cannot help you.

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Wed, 17 Oct 2007 05:00:18 GMT

Hi,

Please check the following piece of code having issues.

SELECT B~PERNR

D~WERKS

D~GSBER

D~BTRTL

E~NAME1

F~BTEXT

B~NACHN

B~VORNA

C~USRID_LONG

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM HRP1001 AS A

INNER JOIN PA0001 AS D

ON A_{OBJID = D}PERNR

INNER JOIN T500P AS E

ON D_{WERKS = E}PERSA

LEFT OUTER JOIN T001P AS F

ON D_{WERKS = F}WERKS AND

D_{BTRTL = F}BTRTL

INNER JOIN PA0002 AS B

ON A_{OBJID = B}PERNR

INNER JOIN PA0105 AS C

ON B_{PERNR = C}PERNR

WHERE A~PLVAR EQ C_ACT_PLAN AND

A~OTYPE EQ C_PERSON AND

A~SOBID EQ P1_BETID AND

( A~BEGDA LE SY-DATUM AND

A~ENDDA GT SY-DATUM ) AND

A~RSIGN EQ C_TOPDOWN AND

A~RELAT EQ C_ADMNBY AND

A~SCLAS EQ C_BET_TYPE AND

A~ISTAT EQ C_FIRMED AND

( D~BEGDA LE SY-DATUM AND

D~ENDDA GT SY-DATUM ) AND

( B~BEGDA LE SY-DATUM AND

B~ENDDA GT SY-DATUM ) AND

C~SUBTY EQ C_SUBTYP_MAIL AND

C~USRTY EQ C_SUBTYP_MAIL AND

( C~BEGDA LE SY-DATUM AND

C~ENDDA GT SY-DATUM ).

Please suggest me an efficient way of restructuring this.

Thanks

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Wed, 17 Oct 2007 05:01:38 GMT

Please check this piece of code.

SELECT B~PERNR

D~WERKS

D~GSBER

D~BTRTL

E~NAME1

F~BTEXT

B~NACHN

B~VORNA

C~USRID_LONG

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM HRP1001 AS A

INNER JOIN PA0001 AS D

ON A_{OBJID = D}PERNR

INNER JOIN T500P AS E

ON D_{WERKS = E}PERSA

LEFT OUTER JOIN T001P AS F

ON D_{WERKS = F}WERKS AND

D_{BTRTL = F}BTRTL

INNER JOIN PA0002 AS B

ON A_{OBJID = B}PERNR

INNER JOIN PA0105 AS C

ON B_{PERNR = C}PERNR

WHERE A~PLVAR EQ C_ACT_PLAN AND

A~OTYPE EQ C_PERSON AND

A~SOBID EQ P1_BETID AND

( A~BEGDA LE SY-DATUM AND

A~ENDDA GT SY-DATUM ) AND

A~RSIGN EQ C_TOPDOWN AND

A~RELAT EQ C_ADMNBY AND

A~SCLAS EQ C_BET_TYPE AND

A~ISTAT EQ C_FIRMED AND

( D~BEGDA LE SY-DATUM AND

D~ENDDA GT SY-DATUM ) AND

( B~BEGDA LE SY-DATUM AND

B~ENDDA GT SY-DATUM ) AND

C~SUBTY EQ C_SUBTYP_MAIL AND

C~USRTY EQ C_SUBTYP_MAIL AND

( C~BEGDA LE SY-DATUM AND

C~ENDDA GT SY-DATUM ).

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Wed, 17 Oct 2007 05:02:22 GMT

Please check the select query.

SELECT B~PERNR

D~WERKS

D~GSBER

D~BTRTL

E~NAME1

F~BTEXT

B~NACHN

B~VORNA

C~USRID_LONG

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM HRP1001 AS A

INNER JOIN PA0001 AS D

ON A_{OBJID = D}PERNR

INNER JOIN T500P AS E

ON D_{WERKS = E}PERSA

LEFT OUTER JOIN T001P AS F

ON D_{WERKS = F}WERKS AND

D_{BTRTL = F}BTRTL

INNER JOIN PA0002 AS B

ON A_{OBJID = B}PERNR

INNER JOIN PA0105 AS C

ON B_{PERNR = C}PERNR

WHERE A~PLVAR EQ C_ACT_PLAN AND

A~OTYPE EQ C_PERSON AND

A~SOBID EQ P1_BETID AND

( A~BEGDA LE SY-DATUM AND

A~ENDDA GT SY-DATUM ) AND

A~RSIGN EQ C_TOPDOWN AND

A~RELAT EQ C_ADMNBY AND

A~SCLAS EQ C_BET_TYPE AND

A~ISTAT EQ C_FIRMED AND

( D~BEGDA LE SY-DATUM AND

D~ENDDA GT SY-DATUM ) AND

( B~BEGDA LE SY-DATUM AND

B~ENDDA GT SY-DATUM ) AND

C~SUBTY EQ C_SUBTYP_MAIL AND

C~USRTY EQ C_SUBTYP_MAIL AND

( C~BEGDA LE SY-DATUM AND

C~ENDDA GT SY-DATUM ).

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Wed, 17 Oct 2007 07:45:42 GMT

then coding does not really help much,

you must give the primary keys of the tables, and the number of entries in the tables (in your system) and the selectivity of the fields.

It should be possible to split the join into 2 part and mix up the 2 results.

Or start with a smaller join and add the rest with for all entries.

The whole analysis is a bit cumbersome and needs some experience.

Siegfried

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Wed, 17 Oct 2007 07:54:57 GMT

Dear Rupesh Kumar,

I had joined 5 tables before. Here are some tips for joining tables:

1. List out all the primary keys for your tables.

2. INNER JOIN all your tables in sequence that starts with least primary key.

3. The sequence of fields must in same order like the tables.

p/s: I found that you had used LEFT OUTER JOIN in your code. Try not to use left outer join if possible because more records will be selected and will decreased the performance. You can refer to SAP Library for more info about concept on LEFT OUTER JOIN.

Hope this will help.

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Wed, 17 Oct 2007 18:52:48 GMT

Hi Rupesh,

I do not understand why this select is causing you a performance issue. I tried replicating it in my system and it took 1 second to execute and retrieved about 1400 records.

A closer look at your select reveals that you are not using any select options. You seem to be using constants and a parameter (all single values). You also seem to have many conditions specified in the where cluase. This too should work in your favour.

To test your issue I created 2 programs. One with your code (using joins) and the other with FOR ALL ENTRIES. Both the programs took about a second to run and retrieved the same number of records. I am attaching both these pieces of code below for your perusal.

Code similar to your code using JOINS.

CONSTANTS: c_act_plan(2)    TYPE c VALUE 'U1'      ,
           c_person(1)      TYPE c VALUE 'P'       ,
           p1_betid(8)      TYPE c VALUE '50005316',
           c_topdown(1)     TYPE c VALUE 'A'       ,
           c_admnby(3)      TYPE c VALUE '032'     ,
           c_bet_type(1)    TYPE c VALUE 'Q'       ,
           c_firmed(1)      TYPE c VALUE '1'       ,
           c_subtyp_mail(4) TYPE c VALUE '0001'    .


TYPES: BEGIN OF ty_output,
         pernr      TYPE pa0002-pernr     ,
         werks      TYPE pa0001-werks     ,
         gsber      TYPE pa0001-gsber     ,
         btrtl      TYPE pa0001-btrtl     ,
         name1      TYPE t500p-name1      ,
         btext      TYPE t001p-btext      ,
         nachn      TYPE pa0002-nachn     ,
         vorna      TYPE pa0002-vorna     ,
         usrid_long TYPE pa0105-usrid_long,
       END OF ty_output.

DATA: t_output TYPE TABLE OF ty_output .


SELECT b~pernr
d~werks
d~gsber
d~btrtl
e~name1
f~btext
b~nachn
b~vorna
c~usrid_long
INTO CORRESPONDING FIELDS OF TABLE t_output
FROM hrp1001 AS a
INNER JOIN pa0001 AS d
ON a~objid = d~pernr
INNER JOIN t500p AS e
ON d~werks = e~persa
LEFT OUTER JOIN t001p AS f
ON d~werks = f~werks AND
d~btrtl = f~btrtl
INNER JOIN pa0002 AS b
ON a~objid = b~pernr
INNER JOIN pa0105 AS c
ON b~pernr = c~pernr
WHERE a~plvar EQ c_act_plan AND
a~otype EQ c_person AND
a~sobid EQ p1_betid AND
( a~begda LE sy-datum AND
a~endda GT sy-datum ) AND
a~rsign EQ c_topdown AND
a~relat EQ c_admnby AND
a~sclas EQ c_bet_type AND
a~istat EQ c_firmed AND
( d~begda LE sy-datum AND
d~endda GT sy-datum ) AND
( b~begda LE sy-datum AND
b~endda GT sy-datum ) AND
c~subty EQ c_subtyp_mail AND
c~usrty EQ c_subtyp_mail AND
( c~begda LE sy-datum AND
c~endda GT sy-datum ).

Code using FOR ALL ENTRIES.

CONSTANTS: c_act_plan(2)    TYPE c VALUE 'U1'      ,
           c_person(1)      TYPE c VALUE 'P'       ,
           p1_betid(8)      TYPE c VALUE '50005316',
           c_topdown(1)     TYPE c VALUE 'A'       ,
           c_admnby(3)      TYPE c VALUE '032'     ,
           c_bet_type(1)    TYPE c VALUE 'Q'       ,
           c_firmed(1)      TYPE c VALUE '1'       ,
           c_subtyp_mail(4) TYPE c VALUE '0001'    .


TYPES: BEGIN OF ty_hrp1001,
         objid TYPE hrp1001-objid,
       END OF ty_hrp1001,

       BEGIN OF ty_pa0001,
         pernr TYPE pa0001-pernr,
         subty TYPE pa0001-subty,
         objps TYPE pa0001-objps,
         sprps TYPE pa0001-sprps,
         endda TYPE pa0001-endda,
         begda TYPE pa0001-begda,
         seqnr TYPE pa0001-seqnr,
         werks TYPE pa0001-werks,
         gsber TYPE pa0001-gsber,
         btrtl TYPE pa0001-btrtl,
       END OF ty_pa0001,

       BEGIN OF ty_t500p,
         persa TYPE t500p-persa,
         name1 TYPE t500p-name1,
       END OF ty_t500p,

       BEGIN OF ty_t001p,
         werks TYPE t001p-werks,
         btrtl TYPE t001p-btrtl,
         btext TYPE t001p-btext,
       END OF ty_t001p,

       BEGIN OF ty_pa0002,
         pernr TYPE pa0002-pernr,
         subty TYPE pa0002-subty,
         objps TYPE pa0002-objps,
         sprps TYPE pa0002-sprps,
         endda TYPE pa0002-endda,
         begda TYPE pa0002-begda,
         seqnr TYPE pa0002-seqnr,
         nachn TYPE pa0002-nachn,
         vorna TYPE pa0002-vorna,
       END OF ty_pa0002,

       BEGIN OF ty_pa0105,
         pernr      TYPE pa0105-pernr     ,
         subty      TYPE pa0105-subty     ,
         objps      TYPE pa0105-objps     ,
         sprps      TYPE pa0105-sprps     ,
         endda      TYPE pa0105-endda     ,
         begda      TYPE pa0105-begda     ,
         seqnr      TYPE pa0105-seqnr     ,
         usrid_long TYPE pa0105-usrid_long,
       END OF ty_pa0105,

       BEGIN OF ty_output,
         pernr      TYPE pa0002-pernr     ,
         werks      TYPE pa0001-werks     ,
         gsber      TYPE pa0001-gsber     ,
         btrtl      TYPE pa0001-btrtl     ,
         name1      TYPE t500p-name1      ,
         btext      TYPE t001p-btext      ,
         nachn      TYPE pa0002-nachn     ,
         vorna      TYPE pa0002-vorna     ,
         usrid_long TYPE pa0105-usrid_long,
       END OF ty_output.


DATA: w_hrp1001     TYPE                 ty_hrp1001,
      t_hrp1001     TYPE        TABLE OF ty_hrp1001,
      t_hrp1001_tmp TYPE        TABLE OF ty_hrp1001,
      w_pa0001      TYPE                 ty_pa0001 ,
      t_pa0001      TYPE SORTED TABLE OF ty_pa0001
        WITH NON-UNIQUE KEY pernr                  ,
      t_pa0001_tmp  TYPE        TABLE OF ty_pa0001 ,
      w_t500p       TYPE                 ty_t500p  ,
      t_t500p       TYPE HASHED TABLE OF ty_t500p
        WITH UNIQUE KEY persa                      ,
      w_t001p       TYPE                 ty_t001p  ,
      t_t001p       TYPE HASHED TABLE OF ty_t001p
        WITH UNIQUE KEY werks btrtl                ,
      w_pa0002      TYPE                 ty_pa0002 ,
      t_pa0002      TYPE SORTED TABLE OF ty_pa0002
        WITH NON-UNIQUE KEY pernr                  ,
      t_pa0002_tmp  TYPE        TABLE OF ty_pa0002 ,
      w_pa0105      TYPE                 ty_pa0105 ,
      t_pa0105      TYPE SORTED TABLE OF ty_pa0105
        WITH NON-UNIQUE KEY pernr                  ,
      w_output      TYPE                 ty_output ,
      t_output      TYPE        TABLE OF ty_output .


SELECT objid
  FROM hrp1001
  INTO TABLE t_hrp1001
  WHERE otype EQ c_person
  AND   plvar EQ c_act_plan
  AND   rsign EQ c_topdown
  AND   relat EQ c_admnby
  AND   istat EQ c_firmed
  AND   begda LE sy-datum
  AND   endda GT sy-datum
  AND   sclas EQ c_bet_type
  AND   sobid EQ p1_betid.

IF sy-subrc EQ 0.
  SORT t_hrp1001 BY objid.
ENDIF.


IF NOT t_hrp1001[] IS INITIAL.

  t_hrp1001_tmp[] = t_hrp1001[].

  DELETE ADJACENT DUPLICATES FROM t_hrp1001_tmp
    COMPARING objid.

  SELECT pernr
         subty
         objps
         sprps
         endda
         begda
         seqnr
         werks
         gsber
         btrtl
    FROM pa0001
    INTO TABLE t_pa0001
    FOR ALL ENTRIES IN t_hrp1001_tmp
    WHERE pernr EQ t_hrp1001_tmp-objid
    AND   endda GT sy-datum
    AND   begda LE sy-datum.


  SELECT pernr
         subty
         objps
         sprps
         endda
         begda
         seqnr
         nachn
         vorna
    FROM pa0002
    INTO TABLE t_pa0002
    FOR ALL ENTRIES IN t_hrp1001_tmp
    WHERE pernr EQ t_hrp1001_tmp-objid
    AND   endda GT sy-datum
    AND   begda LE sy-datum.

ENDIF.


IF NOT t_pa0001[] IS INITIAL.

  t_pa0001_tmp[] = t_pa0001[].

  SORT t_pa0001_tmp BY werks btrtl.

  DELETE ADJACENT DUPLICATES FROM t_pa0001_tmp COMPARING werks btrtl.

  SELECT werks
         btrtl
         btext
    FROM t001p
    INTO TABLE t_t001p
    FOR ALL ENTRIES IN t_pa0001_tmp
    WHERE werks EQ t_pa0001_tmp-werks
    AND   btrtl EQ t_pa0001_tmp-btrtl.


  DELETE ADJACENT DUPLICATES FROM t_pa0001_tmp COMPARING werks.

  SELECT persa
         name1
    FROM t500p
    INTO TABLE t_t500p
    FOR ALL ENTRIES IN t_pa0001_tmp
    WHERE persa EQ t_pa0001_tmp-werks.

ENDIF.


IF NOT t_pa0002[] IS INITIAL.

  t_pa0002_tmp[] = t_pa0002[].

  DELETE ADJACENT DUPLICATES FROM t_pa0002_tmp COMPARING pernr.

  SELECT pernr
         subty
         objps
         sprps
         endda
         begda
         seqnr
         usrid_long
    FROM pa0105
    INTO TABLE t_pa0105
    FOR ALL ENTRIES IN t_pa0002_tmp
    WHERE pernr EQ t_pa0002_tmp-pernr
    AND   subty EQ c_subtyp_mail
    AND   endda GT sy-datum
    AND   begda LE sy-datum
    AND   usrty EQ c_subtyp_mail.

ENDIF.


REFRESH t_output.

LOOP AT t_hrp1001 INTO w_hrp1001.

  LOOP AT t_pa0001 INTO w_pa0001
    WHERE pernr EQ w_hrp1001-objid.

    READ TABLE t_t500p INTO w_t500p WITH KEY persa = w_pa0001-werks
                                             TRANSPORTING
                                               name1.

    CHECK sy-subrc EQ 0.

    READ TABLE t_t001p INTO w_t001p WITH KEY werks = w_pa0001-werks
                                             btrtl = w_pa0001-btrtl
                                             TRANSPORTING
                                               btext.

    LOOP AT t_pa0002 INTO w_pa0002
      WHERE pernr EQ w_hrp1001-objid.

      LOOP AT t_pa0105 INTO w_pa0105
        WHERE pernr EQ w_pa0002-pernr.

        w_output-pernr      = w_pa0002-pernr     .
        w_output-werks      = w_pa0001-werks     .
        w_output-gsber      = w_pa0001-gsber     .
        w_output-btrtl     = w_pa0001-btrtl      .
        w_output-name1      = w_t500p-name1      .
        w_output-btext      = w_t001p-btext      .
        w_output-nachn      = w_pa0002-nachn     .
        w_output-vorna      = w_pa0002-vorna     .
        w_output-usrid_long = w_pa0105-usrid_long.

        APPEND w_output TO t_output.

        CLEAR w_output.

      ENDLOOP.

    ENDLOOP.

  ENDLOOP.

ENDLOOP.

Let me know if this helps.

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Thu, 18 Oct 2007 04:28:29 GMT

Hi Rupesh,

Try Using FOR ALL ENTRIES.

Reward if useful.

Regards,

Chitra

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Thu, 18 Oct 2007 04:55:25 GMT

Hi Chitra,

Thanks for the help..

i am trying for the one but the code is a bit complex.

let me check how it works.

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Thu, 18 Oct 2007 04:59:50 GMT

Hi Mark,

Thanks really for your help....let me try with the piece of code you suggested but the code seems to be very lengthy..for that only i was using the joins....Looking for more inputs from your side.

Thanks,

Rupesh

Re: Performance issue with a select statement using 5 joins.......Help!!

Former Member — Thu, 18 Oct 2007 09:20:29 GMT

Hello,

You may use more than 3 tables into a inner join.

Do not use for all entries systematically, It is not very good when your internal table contains more than 10 000 records.

If you have more than 3 tables, create a view. It is easier to manage.

Your performance issue may come from your where condition, by using fields without index, or not primary key. the quality of your inner join may give bad performance also.

You must use ST04 transaction to identify the purpose of bad performance.

Yann SZWEC

PS: I have already seen a select with 8 joins, working very well.