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

Problem with Inner Join

Hi All,

I am new to SAP and have been told by all abapers that using joins is a major performance issue but in below select i am using inner join with key fields on the tables because if i don't use joins then i have to use loops which will again a performace issue so please suggest me that how in my case joins is a performance issue and if it is then what's best way to do.

select

vk~vbeln

vk~erdat

vk~vkgrp

vk~kunnr

vk~lifsk " lifsk

k1a~name1 as sotonm

pa~kunnr as kunnr_pa

k1b~name1 as shtonm

vk~gwldt

vk~vdatu

vk~bstnk

tv~bezei

from vbak as vk

inner join

kna1 as k1a on vkkunnr = k1akunnr

inner join

vbpa as pa on vkvbeln = pavbeln and

pa~parvw = 'WE' and

pa~posnr = ''

inner join

kna1 as k1b on pakunnr = k1bkunnr

inner join

tvgrt as tv on vkvkgrp = tvvkgrp

into corresponding fields of table ord_hdr

where vk~lifsk in dblock.

Thanks in advance,

Navdeep singh

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 07, 2007 at 05:26 AM

    Hi Navdeep ,

    Instate of Inner Join used select for all enteries.

    Plz fellow this code and test it SE30 Tcode:Just Test Inner join Program and This code also , Plz let me known .

    AT SELECTION-SCREEN.

    SELECT vbeln

    kunnr

    vkorg

    vtweg

    spart

    FROM vbak

    INTO TABLE it_vbak

    WHERE vbeln IN s_vbeln

    AND vkorg IN s_vkorg

    AND erdat IN s_erdat.

    IF sy-subrc NE 0.

    MESSAGE ' NO RECORDS EXIST ! ' TYPE 'E'.

    ENDIF.

    START-OF-SELECTION.

    SELECT vbeln

    posnr

    matnr

    brgew

    ntgew

    gewei

    netwr

    waerk

    FROM vbap

    INTO TABLE it_vbap

    FOR ALL ENTRIES IN it_vbak

    WHERE vbeln = it_vbak-vbeln .

    SELECT kunnr

    name1

    telf1

    FROM kna1

    INTO TABLE it_kna1

    FOR ALL ENTRIES IN it_vbak

    WHERE kunnr = it_vbak-kunnr .

    Regards,

    Nihar Swain .

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 07, 2007 at 05:45 AM

    Hi Navdeep,

    It is advisible not to use more than one inner join.

    so use inner joins seperately and fill internal tables, then combine them using "for all entries".

    For all entries can be used like:

    SELECT kunnr

    FROM kna1

    INTO TABLE it_kna1

    FOR ALL ENTRIES IN it_vbak

    WHERE kunnr = it_vbak-kunnr .

    Regards,

    Sheron

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 07, 2007 at 07:54 AM

    When you want to optimize inner joins, then you must invest some time, use FOR ALL ENTRIES does noot help.

    You must check the primary keys and secondary key, if there are any, of all tables and figure out, which access would be must selective, i.e. reduce the results set most. And then you must check which table should be taken next and so on.

    Depending on your situation such an analysis can be simple or very complicated.

    After figuring out, whether there is a good solution you must check what the database is actually doing. Use SQL Trace ... see a new blog under my name.

    In many cases gthe database is not doing to optimal access, then you can change the behavior of the database either by adding new indices or by using hints.

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 07, 2007 at 01:24 PM

    Navdeep - please check these two:

    FOR" target="_blank">http://blogs.ittoolbox.com/sap/db2/archives/for-all-entries-vs-db2-join-8912">FOR ALL ENTRIES vs DB2 JOIN

    <a href="/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better">JOINS vs. FOR ALL ENTRIES - Which Performs Better?</a>

    Rob

    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.