Skip to Content
author's profile photo Former Member
Former Member

join statement.

Hi ,

I have given following statement.

select vbak~vbeln

vbak~bstdk

vbak~bstnk

vbak~knumv

vbak~lifsk

vbak~augru

vbak~erdat

vbak~spart

vbak~vkbur

vbap~arktx

zro_downpayment~branch_date

zro_downpayment~bldat

zro_downpayment~belnr

zro_downpayment~payment_scheme

zro_downpayment~payable_to

zro_downpayment~payment_method

zro_downpayment~contract

zro_downpayment~payee

zro_downpayment~supplier

zro_downpayment~soldto

into table it_vbak

from vbak left outer join vbap on vbakvbeln = vbapvbeln

left outer join zro_downpayment on vbakvbeln = zro_downpaymentvbeln

where vbak~erdat in s_erdat

and vbak~lifsk eq c_lifsk1

and vbak~vkorg eq p_vkorg

and vbak~vtweg in s_vtweg

and vbak~spart in s_spart

and vbak~vkbur in s_off.

in the above statement vbakerdat is used only 15-20% time i.e. user generally does not enter values in s_erdat. Index of vbak is on erdat field. Can you please guide me how to tune this statement to avoid vbakerdat and use different where clause so that program is executed efficiently.

Regards,

Santosh

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 16, 2009 at 11:32 AM

    Hi,

    Please use Views instead of joins.

    Or Use seperate select query join for vbak and vbap and seperate query for ztable.

    Avoid using left outer joins.

    Regards,

    Nandha

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 16, 2009 at 11:47 AM

    Hi Santosh ,

    As per my understanding of your logic , you need to fetch records from zro_downpayment based on VBAK and VBAP entries based on some conditions ,

    There are many ways to do the same , you need to identify the best in your case.

    You can first fetch the records from VBAK and VBAP in one go in an internal table .

    Now get the unique VBELN from this internal table.

    Use FOR ALL ENTRIES to fetch records from database table zro_downpayment .

    Now, collect all the records in resultant internal table .

    Hope this helps you.

    Note : This can increase the code processing time , but will reduce time for database operation.

    Also, use of join with more than 2 tables should not be prefered.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      >

      > Use FOR ALL ENTRIES to fetch records from database table zro_downpayment .

      > Note : This can increase the code processing time , but will reduce time for database operation.

      >

      > Also, use of join with more than 2 tables should not be prefered.

      I'm curious to know how you came to this conclusion that Join is not preffered for more than 2 tables and FOR ALL entries is better in database operation.

      BR,

      Advait

  • author's profile photo Former Member
    Former Member
    Posted on Sep 16, 2009 at 12:53 PM

    A few pointers

    - You've joined VBAK and VBAP , but are not selecting the posnr field anywhere. This may lead to inconsistencies.

    - There is no index on VBAP so it is diffcult to refine your query.

    Apart from that since you are using an outer join, the db system creates a temporary table with all the records as per the ON condition, then fetch all remaining records from the left hand side table and put zero values in the fields of the right hand side table. And then applies the where condition.

    Thus for outer joins,the number of records fetched will be higher if the fields in the where clause are left blank.

    Only thing that I can think of it to make the erdat mandatory. But not a very good solution perhaps.

    Hope someone can come up with a better solution.

    BR,

    Advait

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 16, 2009 at 10:43 PM

    Hi Santosh,

    You have told us that the user only enters ERDAT 15-20% of the time however you have not told us what the user enters most of the time. Usually you need to inform your users that they cannot run the report open (without any selection criteria) online. If they insist on doing that tell them to run the program in the background.

    With regards to VBAK you don't have much choice. SAP provides secondary indexes in ERDAT and AUDAT in addition to the primary index on VBELN. You need to talk to you users and explain to them this problem.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.