topic Re: Problem with Inner Join in Application Development and Automation Discussions

Problem with Inner Join

Former Member — Fri, 07 Sep 2007 05:09:22 GMT

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 vk_{kunnr = k1a}kunnr

inner join

vbpa as pa on vk_{vbeln = pa}vbeln and

pa~parvw = 'WE' and

pa~posnr = ''

inner join

kna1 as k1b on pa_{kunnr = k1b}kunnr

inner join

tvgrt as tv on vk_{vkgrp = tv}vkgrp

into corresponding fields of table ord_hdr

where vk~lifsk in dblock.

Thanks in advance,

Navdeep singh

Re: Problem with Inner Join

Former Member — Fri, 07 Sep 2007 05:26:29 GMT

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 .

Re: Problem with Inner Join

Former Member — Fri, 07 Sep 2007 05:45:33 GMT

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

Re: Problem with Inner Join

Former Member — Fri, 07 Sep 2007 07:54:32 GMT

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

Re: Problem with Inner Join

Former Member — Fri, 07 Sep 2007 13:24:51 GMT

Navdeep - please check these two:

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

<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