topic performance wise in Application Development and Automation Discussions

performance wise

Former Member — Sat, 17 Feb 2007 10:43:08 GMT

Hi All,

Performance wise which is best to use.

Inner join statement (if we joining two tables) or For all entries ?

Thanks in Advance

Prasanna.

Re: performance wise

Former Member — Sat, 17 Feb 2007 10:51:39 GMT

Hi,

It depends upon the tables and thier comparing fields

for header and item tables JOIN is good

for some cases if the records are repeatative then delete adjacent duplicate entries and use for all entries

this all should be done by trial and error method.

Thanks

Shiva

Re: performance wise

Former Member — Sat, 17 Feb 2007 10:56:45 GMT

depends like how many tables are u fetching from .

if the tables are more than 3 and they have a finite relationship with keys then better go for inner join , Ex tables MARA MARC MARD MAKT

if these are the tables tehn join can be used to get the data.

cause

mara-matnr = marc-matnr = mard-matnr = makt-matnr

marc-werks = mard-werks

= mard-lgort

if the table hierarchy is not consistent then use for all entries .

like VBAK Kna1 adrc in these cases there is no definite key relation ship so opt for for all entries .

For performance :

Majoritily joins will be used when the programmer is comfortable with teh keys of the table.

case 1.

cause if there is no such thing then program execution will take longer time.

case2.

for all entries is quick compared to inner joins .

But need to handle with more selects .

regards,

vijay

Re: performance wise

Former Member — Sat, 17 Feb 2007 11:11:36 GMT

Hi,

This question has been asked many times before.

Do a search in SDN for "inner join for all entries" and you'll see what I mean!

Really it depends on the number and types of the database tables in question and how many entries you have in your internal table.

If you have 2 tables related by primary key fields (e.g. VBAK & VBAP) then do a join. Quite often, such related tables have database views already created by SAP so have a look at these as a better option.

'for all entries in' tends to be very inefficient with large numbers of records in your internal table because SAP will split the entries (so the SQL statement doesn't get too big) and do multiple selects from the database.

Mike

Re: performance wise

former_member2382 — Sat, 17 Feb 2007 11:32:17 GMT

Its alwys better to go for For All Entries.. Rather than Inner Joins.. U can find the diff which our friends has replied for the same..

Regards,

Parvez

Re: performance wise

Former Member — Sat, 17 Feb 2007 11:36:02 GMT

1. U can join 2 tables by using joins but the performance goes down. This can be overcomed by for all entries.

select-options : s_vbeln for likp-vbeln.

select vbeln erdat lfart

from likp

into table it_likp

where vbeln in s_vbeln.

based on s_vbeln the records will be stored in it_likp. eg, say 24 records selected

if not it_likp[] is initial.

select vbeln posnr matnr

from lips

into table it_lips

for all entries in it_likp

where vbeln eq it_likp-vbeln.

endif.

now , since i am using,

for all entries in it_likp

so the vbeln value of these 24 records (which i have selected in it_likp) will be checked in lips and those records will be stored in it_lips.

Drawback is if the records are not there in it_likp then all the records from lips will be fetched into it_lips.

To overcome this draw back we have to compulsory check

if not it_likp[] is initial.

this means if the internal table it_likp is not initial ie, it is containing some records

Re: performance wise

Clemenss — Sat, 17 Feb 2007 18:23:44 GMT

Hi krishna sri,

it is always a good idea to switch on performance trace in second section, test run select statement, switch off and analyse. Use the toolbox icon to explain select statement. This will show you how joins are achieved, what indexes are used and a cost estimate on the database access.

Use this to decide a couple of time and you will get the experience you are asking for here. Win merits and show solidarity with others and search online help and this forum before posting.

Regards,

Clemens