topic Re: query optimization in Application Development and Automation Discussions

query optimization

Former Member — Tue, 17 Jul 2007 10:32:01 GMT

hello to all,

i've one problem. in a report there was this query present:

SELECT * FROM mkpf WHERE budat IN date AND

MBLNR IN MBLNR.

SELECT * FROM mseg WHERE mblnr = mkpf-mblnr AND

mjahr = mkpf-mjahr.

MOVE-CORRESPONDING mseg TO zmseg.

APPEND zmseg. CLEAR zmseg.

ENDSELECT.

becoz of nested SELECT it was too much time to execute. so i've made following changes:

SELECT mblnr mjahr budat xblnr FROM mkpf into corresponding fields of table tmkpf WHERE budat IN date and MBLNR IN MBLNR.

if sy-subrc eq 0.

SELECT mblnr mjahr bwart matnr werks lifnr ebeln ebelp FROM mseg into corresponding fields of table zmseg for all entries in tmkpf WHERE mblnr = tmkpf-mblnr AND mjahr = tmkpf-mjahr.

endif.

but when i checked ZMSEG table for both the queries it is showing different number of records.

what could be goin wrong with the query? kindly help.........

thank you.

Re: query optimization

Former Member — Tue, 17 Jul 2007 10:34:52 GMT

SELECT mblnr mjahr budat xblnr FROM mkpf into corresponding fields of table tmkpf WHERE budat IN date and MBLNR IN MBLNR.

if sy-subrc eq 0.

SORT TABLE TMKPF BY MBLNR MJAHR.

DELETE ADJACENT DUPLICATES FROM TABLE TMKPF COMPARING MBLNR MJAHR.

SELECT mblnr mjahr bwart matnr werks lifnr ebeln ebelp FROM mseg into corresponding fields of table zmseg for all entries in tmkpf WHERE mblnr = tmkpf-mblnr AND mjahr = tmkpf-mjahr.

endif.

Do the necessary modifications as shown above.

Hope this helps you.

Regards,

Pavan.

Re: query optimization

Former Member — Tue, 17 Jul 2007 10:35:26 GMT

Hi,

Better use this view...

WB2_V_MKPF_MSEG

Then u need only one Select...

Reward points if it helps..

Regards,

Omkar.

Re: query optimization

Former Member — Tue, 17 Jul 2007 10:35:30 GMT

Hi,

Instead of using SELECT...ENDSELECT why don't you try using SELECT INTO TABLE <itab> WHERE <cond>.

If you do so then there won't be any nested selects.

Hope it was useful.

Thanks,

Sandeep.

Re: query optimization

Former Member — Tue, 17 Jul 2007 10:36:15 GMT

Hi,

First select data into MKPF.

SELECT * FROM mkpf into table it_mkpf WHERE budat IN date AND

MBLNR IN MBLNR.

then if not it_mkpf[] is initial.

SELECT * FROM mseg into table it_mseg

for all entries in it_mkpf

WHERE mblnr = it_mkpf-mblnr AND

mjahr = it_mkpf-mjahr.

endif.

Then combine both itabs by looping it.

loop at it_mkpf

read table it_mseg.....

store it in it_output.

endloop.

Also search for more index and keys.

Reward if useful!

Re: query optimization

Former Member — Tue, 17 Jul 2007 10:37:18 GMT

hi,

check it with removing the sy-subrc condition.

Re: query optimization

Former Member — Tue, 17 Jul 2007 10:37:20 GMT

The second query is fine and good

use the 2nd Option queires using just fields and for all entries of mkpf..

first one Select * ..endselect..not advisable..

<b>Reward points for useful Answers</b>

Regards

Anji