topic Re: Select query u0096 Performance in Application Development and Automation Discussions

Select query u0096 Performance

Former Member — Fri, 15 Feb 2008 12:03:55 GMT

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

Re: Select query u0096 Performance

Former Member — Fri, 15 Feb 2008 12:06:13 GMT

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

Re: Select query u0096 Performance

Former Member — Fri, 15 Feb 2008 12:06:31 GMT

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.

Re: Select query u0096 Performance

Former Member — Fri, 15 Feb 2008 12:09:12 GMT

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

Re: Select query u0096 Performance

Former Member — Fri, 15 Feb 2008 12:09:36 GMT

Include Pernr in where condition

Re: Select query u0096 Performance

Former Member — Fri, 15 Feb 2008 12:10:53 GMT

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

Re: Select query u0096 Performance

Former Member — Fri, 15 Feb 2008 12:13:55 GMT

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

Re: Select query u0096 Performance

Former Member — Fri, 15 Feb 2008 12:20:50 GMT

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..

Re: Select query u0096 Performance

Former Member — Fri, 15 Feb 2008 15:07:54 GMT

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