09-07-2007 6:09 AM
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
09-07-2007 6: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 .
09-07-2007 6: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
09-07-2007 8: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
09-07-2007 2:24 PM
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