Select query u0096 Performance

[Former Member]

Hi Friends,

How can I improve the performance of this select query?

*-Fetch Actions / Organizational Assignment / Personal Data

SELECT a~pernr

b~kostl

b~orgeh

b~plans

b~ename

c~nachn

c~vorna

c~rufnm

INTO TABLE lt_emp_pa0000

FROM pa0000 AS a

INNER JOIN pa0001 AS b ON a_{pernr = b}pernr

INNER JOIN pa0002 AS c ON b_{pernr = c}pernr

WHERE a~sprps = space

AND a~endda GE p_date

AND a~begda LE p_date

AND a~stat2 = p_status

AND b~sprps = space

AND b~endda GE p_date

AND b~begda LE p_date

AND b~werks IN s_werks

AND b~persg IN s_persg

AND b~persk IN s_persk

AND b~abkrs IN s_abkrs

AND c~sprps = space

AND c~begda LE p_date

AND c~endda GE p_date.

Thanks,

Vimal

[Former Member]

Hi

You need to make use of 'FOR ALL ENTRIES' and write 3 separate selects instead of using joins. Use joins only when there is a header-item relationship. In all other cases, for all entries is more efficient than joins.

Thanks

Vijay

PLZ Reward points if helpful

[Former Member]

Hi,

Here instead of inner join u can use for all entries keyword,

like follows,

first select from one table into itab.

then,

if itab[] is not initial.

selcet from secind table into itab for all entries in itab

where pernr = itab-pernr.

*like that for third databse table.

endif.

finally in the loop all these table gields u can move one final internal table for the output.

then performance will impove.

reward points if useful,

seshu.

[Former Member]

I have tried this option already. But it is not helping me .BTW SAP suggest inner join than for all entries

[Former Member]

Hi Vimal,

Goto transaction SE30, click on tips and tricks, you will get a split screen where you can type your query and check the performance. Try out both join and for all entries, and then check the performance.

THanks

Vijay

PLZ reward points if helpful

[Former Member]

Thanks Vijay for the reply.. I tried pasting this select query in SE30 - Tips & Tricks but when I click mesaure .. it say internal table is not found ..select-option not found.. can you suggest how to measure runtime..

[Former Member]

Include Pernr in where condition

[Former Member]

Thanks for the reply .But I dont have PERNR in selection screen ..

[Former Member]

Your query on PA0001 uses a number of fields that are used in secondary indexes. Before executing this SELECT, make sure that s_werks, s_persg, s_persk or s_abkrs has entries.

Rob