Needs Query Optimization

[Former Member]

Hi All

I have the below query - which is a great performance killer.

SELECT A~VBELN A~POSNR A~MATNR A~KWMENG A~KBMENG
         A~ERDAT A~ERZET A~PSTYV D~AUART E~ETTYP
         E~EDATU
           INTO TABLE INT_FIN
      FROM VBAP AS A
      INNER JOIN VBAK AS D ON D~VBELN EQ A~VBELN AND
                           D~MANDT EQ A~MANDT
      INNER JOIN VBEP AS E ON E~VBELN EQ A~VBELN AND
                           E~POSNR EQ A~POSNR    AND
                          E~MANDT EQ A~MANDT
           WHERE           A~VBELN IN s_VBELN AND
                           D~auart in s_auart AND
                           D~vkorg in s_vkorg AND
                           D~vbtyp eq 'C'     AND
  ( ( matnr LIKE c_prefix_sp AND zz_msposnr NE 0 AND kbmeng EQ 0 )
  OR ( matnr LIKE c_prefix_fp AND kwmeng NE A~kbmeng ) ) AND
                           A~ABGRU EQ SPACE      AND
                           A~MTVFP IN R_MTVFP    AND
                           A~PRCTR IN R_PRCT     AND
                           E~ETENR EQ '1'.
 
  SORT INT_FINBY VBELN POSNR ETTYP.
  DELETE ADJACENT DUPLICATES FROM INT_FIN COMPARING VBELN POSNR.
  CHECK NOT INT_FIN[] IS INITIAL.
 
  SELECT VBELN UVALL CMGST INTO TABLE INT_VBUK
          FROM VBUK FOR ALL ENTRIES IN INT_FIN
          WHERE VBELN = INT_FIN-VBELN AND
                UVALL NE 'A'.
  SORT INT_VBUK BY VBELN.
  DELETE ADJACENT DUPLICATES FROM INT_VBUK COMPARING VBELN.

How can I optimize this? Please help me to rewrite the same.

Thx

[Former Member]

Hi,

Try to use inner join between two tables only.

Inner join used more than two tables degrade the performance.

After selection data from two tables using inner join used FOR ALL ENTERIES statement.

Hope it helps.

Regards

Rajesh Kumar

[Former Member]

Hi Rajesh/Ashit

Thx for ur suggestion. But I have tried that way as well. Noted that the performance again degraded after that.

I have a doubt: Instead of using this inner join, if I use separate SELECT statements for each table, how will it affect the performance?

Like,

SELECT from VBAK....

SELECT from VBAP....

SELECT from VBEP....

SELECT from VBUK....

Plz comment.

[Former Member]

Hi,

Go for all entries concept and collect the condition field in hash internal table which will be used for all entries.

Thanks,

Asit Purbey.

[Former Member]

You may map the quesry in a new database view and fetch the data in one go this will increase the performance of the query as database view will take care of all the conditions for views and all. if you use seprate select statements then the data fetched would be high in number and then would be discarded, it is not adviseable as it will degrade the performance going on long way.

Edited by: ROHIT SHARMA on Mar 17, 2009 6:31 AM

[Nawanandana]

hi

you can perfomance several way this program

1. You have to remove the inner joint and insted of that u can u select end select command or

 SELECT VBELN SFAKN FROM VBRK 
      INTO CORRESPONDING FIELDS OF TABLE IT_VBEP
      FOR ALL ENTRIES IN IT_VBRK
      WHERE SFAKN EQ IT_VBRK-VBELN. 

2.U have to create the INDEX for the VBRK and VBEP tables then it wil be the fast.

Hope this wil be help full

Regard

Nawa

[Former Member]

Hi Nawa

SELECT - ENDSELECT is not a good choice at all!

Could anybody help me out to solve this issue?

Thx

[Former Member]

Thx

[Former Member]

hello,

there is a std view which can be used to get data from vbak & vbap which is WB2_V_VBAK_VBAP2

and also then u can get do inner join on VBEP . i think this will make the query more performing

--- Close the post once you get the solution

[Former Member]

Hi

Thx for ur suggestion.

I have a doubt: Instead of using this inner join, if I use separate SELECT statements for each table, how will it affect the performance - will the performance be better or worse?

Like,

SELECT from VBAK....

SELECT from VBAP....

SELECT from VBEP....

SELECT from VBUK....

Plz comment.

[I355602]

Hi,

I dont think it will be considered as peformance issue.

But you need to identify the common fields in all the internal table populated with different select query and then take a final internal table with all reqd fields and then loop the master table and read other tables to fill the final internal table.

Hope this helps you.

Regards,

Tarun

[Former Member]

Hello,

I would suggest avoid doing that... number of database hits should be less and at the same time if u get data into 4 different tables ... u have to witea logic to consolidate the data ... for which u will have to write loop stmts..... which is again not recommended ....

so dont use 4 queries if u can use views and table tables..... ti get data in 1 query...

--- Close the post once you get the solution

[Former Member]

Hi Bobby,

You can apply select query for different tables saperately and then use Loop At and Read Statement fetch data into final table.

Like you have three internal table it_vbap, it_vbak and it_vbek.

then use :

loop at it_vbap.

Read tablle it_vbak where keyfield = it_vbap-keayfield.

transfer data to some internal table it_test1.

endloop.

loop at it_test1.

read table it_vbek where keyfield = it_test1-keyfield.

transfer data to final table it_final.

endloop.

Hope this will help to increase the performance.

Do not you inner join on three tables at a time.

or you can also create index on tables besed on the condition you are using in where clause.

Regrads,

Sourabh Batwara.

[Former Member]

Moved to the correct forum.

Rob

[Former Member]

SELECT ...
      FROM VBAP AS A
      INNER JOIN VBAK AS D ON D~VBELN EQ A~VBELN AND
                           D~MANDT EQ A~MANDT
      INNER JOIN VBEP AS E ON E~VBELN EQ A~VBELN AND
                           E~POSNR EQ A~POSNR    AND
                          E~MANDT EQ A~MANDT
           WHERE           A~VBELN IN s_VBELN AND
                           D~auart in s_auart AND
                           D~vkorg in s_vkorg AND
                           D~vbtyp eq 'C'     AND
  ( ( matnr LIKE c_prefix_sp AND zz_msposnr NE 0 AND kbmeng EQ 0 )
  OR ( matnr LIKE c_prefix_fp AND kwmeng NE A~kbmeng ) ) AND
                           A~ABGRU EQ SPACE      AND
                           A~MTVFP IN R_MTVFP    AND
                           A~PRCTR IN R_PRCT     AND
                           E~ETENR EQ '1'.

What is that ????

  ( ( matnr LIKE c_prefix_sp AND zz_msposnr NE 0 AND kbmeng EQ 0 )
  OR ( matnr LIKE c_prefix_fp AND kwmeng NE A~kbmeng ) ) AND

adding the aliases would make your statement clearer.

This can not be such a big issue, start with that one.

SELECT ...
      FROM VBAP AS A
      INNER JOIN VBAK AS D 
          ON D~MANDT EQ A~MANDT
          ON D~VBELN EQ A~VBELN 
      INNER JOIN VBEP AS E 
          ON   E~MANDT EQ A~MANDT
          AND E~VBELN EQ A~VBELN 
          AND E~POSNR EQ A~POSNR   
    WHERE   A~VBELN IN s_VBELN.

How many records are in s_VBELN? Check number of entries of all three tables

and use the alias of the smallest table.

How does that work?

Add the other conditions, without the complicated thing.

At the end add the complicated condition with the correct aliases.

Siegfried

[Former Member]

HELP <<whinging removed >>

Edited by: Rob Burbank on Mar 24, 2009 9:10 AM

[Rushikesh_Yeole]

performance is degraded due to inner join of many DIFFERENT tables.If possible group the fields of same table. maintain the proper indexing.

[Rushikesh_Yeole]

This message was moderated.

[Rushikesh_Yeole]

This message was moderated.