Performance issue inner join vs FAE

[Former Member]

Hi All,

I know that many people are probably tired of seeing discussions on Inner Join VS for all entries but i'm having performance problems and I was hoping someone might be able to point me in the right direction in terms of what's wrong with my system.

I have a report that is running slowly and I've been doing some basic optimizations, nothing fancy. I came accross two select statements:

SELECT  DISTINCT

               vbfa~vbeln AS   belnr

               vbak~kunnr

               bkpf~budat

               vbap~ps_psp_pnr

         INTO  CORRESPONDING FIELDS OF TABLE lt_hb_info2

         FROM  vbap

         JOIN  vbfa

           ON  vbfa~vbelv        EQ  vbap~vbeln

         JOIN  vbak

           ON  vbap~vbeln        EQ  vbak~vbeln

         JOIN  bkpf

           ON  bkpf~belnr        EQ  vbfa~vbeln

          FOR  ALL entries IN lt_hb_amounts

        WHERE  vbfa~vbeln        EQ  lt_hb_amounts-belnr

          AND  bkpf~budat        LE  s_date.

       SELECT DISTINCT

         proj~pspid

         proj~post1

         prps~pspnr

         INTO CORRESPONDING FIELDS OF TABLE t_proj

         FROM prps

       JOIN  proj

         ON  prps~psphi        EQ  proj~pspnr

       FOR ALL entries IN lt_hb_info2

         WHERE prps~pspnr EQ lt_hb_info2-ps_psp_pnr.

which goes on to loop them together.  Since we're pulling more data than we need I was resonably certain that combining these two statements with an inner join would be faster given that all of the relevant fields here are index fields. (we've added vbap~ps_psp_pnr)

I've changed these two statements to the following:

SELECT

             PROJ~PSPID

             PROJ~POST1

             VBFA~VBELN AS   BELNR

             VBAK~KUNNR

             BKPF~BUDAT

       INTO  CORRESPONDING FIELDS OF TABLE LT_HB_INFO

       FROM  VBAP

       JOIN  PRPS

         ON  VBAP~PS_PSP_PNR   EQ  PRPS~PSPNR

       JOIN  PROJ

         ON  PRPS~PSPHI        EQ  PROJ~PSPNR

       JOIN  VBFA

         ON  VBFA~VBELV        EQ  VBAP~VBELN

       JOIN  VBAK

         ON  VBAP~VBELN        EQ  VBAK~VBELN

       JOIN  BKPF

         ON  BKPF~BELNR        EQ  VBFA~VBELN

        FOR  ALL ENTRIES IN LT_HB_AMOUNTS

      WHERE  VBFA~VBELN        EQ  LT_HB_AMOUNTS-BELNR

*       AND  PROJ~VBUKR        EQ  P_BUKRS

        AND (LV_COND).

Now I wasn't expecting any major improvements but not only did it make it worst, it made the run time go from 4 seconds with the loop to over 32 seconds for the new statement.

I'm trying to understand how it's possible that the database system is so much worst at performing this task than the application server. I've read http://scn.sap.com/thread/1174072 which seemed like a very well informed post and confirmed what I knew about databases. But even if you don't agree with that post what could be causing such a dramatic difference in run time?

I've run my tests multiple times an of course the difference is more stark when I wait a day between tests but regardless of circumstances the difference is always very very large.

Does anyone have any insight they could share?

[Former Member]

Hi All,

I know that many people are probably tired of seeing discussions on Inner Join VS for all entries but i'm having performance problems and I was hoping someone might be able to point me in the right direction in terms of what's wrong with my system.

I have a report that is running slowly and I've been doing some basic optimizations, nothing fancy. I came accross two select statements:

SELECT  DISTINCT

               vbfa~vbeln AS   belnr

               vbak~kunnr

               bkpf~budat

               vbap~ps_psp_pnr

         INTO  CORRESPONDING FIELDS OF TABLE lt_hb_info2

         FROM  vbap

         JOIN  vbfa

           ON  vbfa~vbelv        EQ  vbap~vbeln

         JOIN  vbak

           ON  vbap~vbeln        EQ  vbak~vbeln

         JOIN  bkpf

           ON  bkpf~belnr        EQ  vbfa~vbeln

          FOR  ALL entries IN lt_hb_amounts

        WHERE  vbfa~vbeln        EQ  lt_hb_amounts-belnr

          AND  bkpf~budat        LE  s_date.

       SELECT DISTINCT

         proj~pspid

         proj~post1

         prps~pspnr

         INTO CORRESPONDING FIELDS OF TABLE t_proj

         FROM prps

       JOIN  proj

         ON  prps~psphi        EQ  proj~pspnr

       FOR ALL entries IN lt_hb_info2

         WHERE prps~pspnr EQ lt_hb_info2-ps_psp_pnr.

which goes on to loop them together.  Since we're pulling more data than we need I was resonably certain that combining these two statements with an inner join would be faster given that all of the relevant fields here are index fields. (we've added vbap~ps_psp_pnr)

I've changed these two statements to the following:

SELECT

             PROJ~PSPID

             PROJ~POST1

             VBFA~VBELN AS   BELNR

             VBAK~KUNNR

             BKPF~BUDAT

       INTO  CORRESPONDING FIELDS OF TABLE LT_HB_INFO

       FROM  VBAP

       JOIN  PRPS

         ON  VBAP~PS_PSP_PNR   EQ  PRPS~PSPNR

       JOIN  PROJ

         ON  PRPS~PSPHI        EQ  PROJ~PSPNR

       JOIN  VBFA

         ON  VBFA~VBELV        EQ  VBAP~VBELN

       JOIN  VBAK

         ON  VBAP~VBELN        EQ  VBAK~VBELN

       JOIN  BKPF

         ON  BKPF~BELNR        EQ  VBFA~VBELN

        FOR  ALL ENTRIES IN LT_HB_AMOUNTS

      WHERE  VBFA~VBELN        EQ  LT_HB_AMOUNTS-BELNR

*       AND  PROJ~VBUKR        EQ  P_BUKRS

        AND (LV_COND).

Now I wasn't expecting any major improvements but not only did it make it worst, it made the run time go from 4 seconds with the loop to over 32 seconds for the new statement.

I'm trying to understand how it's possible that the database system is so much worst at performing this task than the application server. I've read http://scn.sap.com/thread/1174072 which seemed like a very well informed post and confirmed what I knew about databases. But even if you don't agree with that post what could be causing such a dramatic difference in run time?

I've run my tests multiple times an of course the difference is more stark when I wait a day between tests but regardless of circumstances the difference is always very very large.

Does anyone have any insight they could share?

[yuri_ziryukin]

First of all,

access to VBFA should not be done with VBELN field. This is clearly stated in one of the SAP notes about performance of SD queries (note 185530).

So you made a mistake at the very beginning.

Next, your code "optimization" is not equal to the original code.

In the original code the first select is always retrieving ONE distinct value for the combination of VBELN, KUNNR, BUDAT and PS_PSP_PNR.

So the second select statement is  getting a smaller subset of records comparing to the JOIN that you did.

[Former Member]

Hi Yuri, thank you for the feedback.

I wasn't aware of that detail regarding VBFA and I thank you for bringing it to my attention. I'll be sure to look into this right away.

yes, I'm also aware that looking at these two sql statements a greater number of records gets selected with the first method but when the tables are looped together the excess statements get removed. This is part of the reason why I was surprised that the more effective method wasn't in fact more efficient.

with that said do you feel any of this explains the dramatic difference in run time? I feel that this might be revealing a problem that isn't related to the specific code but I'm a bit at a loss as to how to look into this problem.

[yuri_ziryukin]

Hello Mathieu,

as I mentioned above:

In the original code the first select is always retrieving ONE distinct value for the combination of VBELN, KUNNR, BUDAT and PS_PSP_PNR.

So the second select statement is  getting a smaller subset of records comparing to the JOIN that you did.

When we talk about comparison of FAE and JOIN we assume that both of them are doing the same thing.

FAE:

1. select from table A

2. select from table B for all entries selected from table A

JOIN:

1. select from table A

2. select from table B for all entries selected from table A, but completely on DB

In your case the point 1 delivers in FAE case much fewer entries because of DISTINCT addition.

Therefore you cannot compare this FAE with the join. It is not exactly the same.

Yuri