topic Re: Query Performance in Application Development and Automation Discussions

Query Performance

rohit_trivedi — Thu, 15 Dec 2005 06:20:00 GMT

Hi Friends,

Here is a query, which is taking a lot of time on Customer system. It gives TIME_OUT short dump even for

as little selection as 500 Business Partners.

Here i would like to add that:

1. All the indexes are properly maintained.

2. No other customer but just one customer system is crashing on this Query.

3. They have already checked that everything is alright

from Database prespective. All DB stats are also uptodate.

All three tables: but020, but100, adrc have around 150000

entries, which is not very big.

Let me know if anyone of you have encountered a similar situation before.

Thanks in advance..

Rohit.

Query is:

.................................

SELECT * FROM but020 INTO TABLE lt_but020

WHERE partner IN

( SELECT partner FROM but100

WHERE partner IN s_partn

AND rltyp IN s_parro )

and ADDRNUMBER in

( SELECT ADDRNUMBER FROM ADRC

WHERE COUNTRY IN S_LAND ).

.........................................

Re: Query Performance

Former Member — Thu, 15 Dec 2005 08:35:12 GMT

Hi!

You can split this SELECT-statement into three separate and thus try to avoid nested loops:

DATA:

it_partner TYPE STANDARD TABLE OF but100-partner WITH HEADER LINE,

it_addr TYPE STANDARD TABLE OF adrc-addrnumber WITH HEADER LINE.

SELECT partner

INTO TABLE it_partner

FROM but100

WHERE partner IN s_partn

AND rltyp IN s_parro.

SELECT addrnumber

INTO TABLE it_addr

FROM adrc

WHERE country IN s_land.

RANGES:

r_partner FOR but020-partner,

r_addr FOR but020-addrnumber.

LOOP AT it_partner.

r_partner-sign = 'I'.

r_partner-option = 'EQ'.

r_partner-low = it_partner.

APPEND r_partner.

ENDLOOP.

LOOP AT it_addr.

r_addr-sign = 'I'.

r_addr-option = 'EQ'.

r_addr-low = it_addr.

APPEND r_addr.

ENDLOOP.

SELECT *

INTO TABLE lt_but020

FROM but020

WHERE partner IN r_partner

AND addrnumber IN r_addr.

Regards,

Maxim.

Re: Query Performance

Former Member — Thu, 15 Dec 2005 09:16:32 GMT

Hi,

For the first two tables simply use join.

then go for FOR ALLENTRIES for 3rd table.

Thanks

Eswar

Re: Query Performance

Former Member — Thu, 15 Dec 2005 09:37:41 GMT

plz remove the nested query and innner join for the first two tables!!

Based on the records u obtain from them make use of "for all entries in" to retrieve the details from the third table!!

Plz reward points if it helped by clicking on the star!!

Regards

Gunjan

Re: Query Performance

Former Member — Thu, 15 Dec 2005 09:44:33 GMT

Hi,

Use Joins instead of writing nested selects

For every single entry UR calling all the three

tables i.e. U hit the database three times .. now U

have 150000 entries , think how many times these

tables will be called.

Thanks,

GSR.

Re: Query Performance

rohit_trivedi — Thu, 15 Dec 2005 09:52:05 GMT

Hi Friends,

All your suggestions make sense, but this should

not happen if i give just 500 Business Parnters on the

selection screen. It just gives TIME_OUT short dump for such lil selection!

Can you guys think of some other reason? Could there be

something wrong with the customer database which they might need to tune?

Is the MANDT field automatically included? I did ST05 analysis on customer system and found out that Index was not used because we didnt specify the MANDT. Is it?

I thought that MANDT is automatically included?

Thanks,

Rohit.

Re: Query Performance

Former Member — Thu, 15 Dec 2005 11:00:18 GMT

Hi,

That might be also one of the reasons.

Giving the entire key all the times might not be

possible.

But the select

( SELECT ADDRNUMBER FROM ADRC

WHERE COUNTRY IN S_LAND ).

will bring in all the entries for the countries given

in the where condition. This happens every time the

selects are called . U just change the selects and Iam

sure it'll work fine.

Regards,

GSR.

Re: Query Performance

Former Member — Thu, 15 Dec 2005 11:39:31 GMT

Hi Rohit,

Split the join and use two or three query.

Store the result of the first query in an internal table and use that as the driver table in the next.

For more better result you can sort the driver and delete the duplicate entries.