Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

need suggestions on my select statement.

aris_hidalgo
Contributor
0 Kudos

Hello experts,

I am having with my select statement since it is running very slow. Normally, the itab it_vendor has records exceeding 7,000. So it loops 7, 000 times and I have 2 select statements which adds to the performance slowdown. Here it is guys:

*Select records records from BSIK and BSAK based on itab it_vendor

LOOP AT it_vendor.

*Select records from BSIK

SELECT belnr lifnr budat buzei gjahr sgtxt dmbtr

shkzg saknr hkont zlspr FROM bsik

INTO TABLE it_bsak

FOR ALL ENTRIES IN it_vendor

WHERE bukrs EQ p_bukrs

AND budat LE p_keydt

AND hkont IN so_saknr

AND lifnr EQ it_vendor-lifnr

AND umsks EQ space

AND umskz EQ space.

*Select records from BSAK

SELECT belnr lifnr budat buzei gjahr sgtxt dmbtr

shkzg saknr hkont zlspr FROM bsak

APPENDING TABLE it_bsak

FOR ALL ENTRIES IN it_vendor

WHERE bukrs EQ p_bukrs

AND augdt GT p_keydt

AND budat LE p_keydt

AND hkont IN so_saknr

AND lifnr EQ it_vendor-lifnr

AND umsks EQ space

AND umskz EQ space.

ENDLOOP.

1 ACCEPTED SOLUTION

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

Since you are using the FOR ALL ENTRIES extension of the select statement, you should not have these inside the loop. What it is correctly doing is getting all the records for all the vendors every time thru the loop. The FOR ALL ENTRIES will get all the records for all of the vendors in one shot, no need to LOOP at the internal table. Make sure that you change your where clauses to how I have them below.




* Get the IT_VENDOR itab here


<b> check not it_vendor[] is initial.
sort it_vendor ascending by lifnr .</b>

*Select records from BSIK
SELECT belnr lifnr budat buzei gjahr sgtxt dmbtr
shkzg saknr hkont zlspr FROM bsik
INTO TABLE it_bsak
FOR ALL ENTRIES IN it_vendor
WHERE<b> lifnr EQ it_vendor-lifnr</b>
and  bukrs EQ p_bukrs
AND budat LE p_keydt
AND hkont IN so_saknr
AND umsks EQ space
AND umskz EQ space.

*Select records from BSAK
SELECT belnr lifnr budat buzei gjahr sgtxt dmbtr
shkzg saknr hkont zlspr FROM bsak
APPENDING TABLE it_bsak
FOR ALL ENTRIES IN it_vendor
WHERE  <b>lifnr EQ it_vendor-lifnr</b>
and bukrs EQ p_bukrs
AND augdt GT p_keydt
AND budat LE p_keydt
AND hkont IN so_saknr
AND umsks EQ space
AND umskz EQ space.
 

Regards,

Rich Heilman

7 REPLIES 7

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

Since you are using the FOR ALL ENTRIES extension of the select statement, you should not have these inside the loop. What it is correctly doing is getting all the records for all the vendors every time thru the loop. The FOR ALL ENTRIES will get all the records for all of the vendors in one shot, no need to LOOP at the internal table. Make sure that you change your where clauses to how I have them below.




* Get the IT_VENDOR itab here


<b> check not it_vendor[] is initial.
sort it_vendor ascending by lifnr .</b>

*Select records from BSIK
SELECT belnr lifnr budat buzei gjahr sgtxt dmbtr
shkzg saknr hkont zlspr FROM bsik
INTO TABLE it_bsak
FOR ALL ENTRIES IN it_vendor
WHERE<b> lifnr EQ it_vendor-lifnr</b>
and  bukrs EQ p_bukrs
AND budat LE p_keydt
AND hkont IN so_saknr
AND umsks EQ space
AND umskz EQ space.

*Select records from BSAK
SELECT belnr lifnr budat buzei gjahr sgtxt dmbtr
shkzg saknr hkont zlspr FROM bsak
APPENDING TABLE it_bsak
FOR ALL ENTRIES IN it_vendor
WHERE  <b>lifnr EQ it_vendor-lifnr</b>
and bukrs EQ p_bukrs
AND augdt GT p_keydt
AND budat LE p_keydt
AND hkont IN so_saknr
AND umsks EQ space
AND umskz EQ space.
 

Regards,

Rich Heilman

0 Kudos

Hi Rich,

So your eman to say that the loop is not required?Thanks a lot for your help!

0 Kudos

Yes, that is correct. The FOR ALL ENTRIES will handle getting all of the records for ALL of the vendors or your IT_VENDOR itab.

Please make sure to award points for helpful answers and mark you post as solved when solved completely. Thanks.

Regards,

Rich Heilman

0 Kudos

Hi again Rich,

Thanks for your very helpful answers. I have one more question. I also have 2 select statements as same as those above but it is using for all entries for itab it_vendor but it_vendor has no records. How will it go about? Thanks a lot!

0 Kudos

You should never use the FOR ALL ENTRIES option on an empty itab. If the internal table itab does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records. that is why Rich put the check not initial statement.

Regards,

Suresh Datti

0 Kudos

If you do not check that IT_VENDOR has records, it will do a full table scan, which will be bad for you. This is why I have added the check the the code sample above.

check not it_vendor[] is initial.

The sorting of IT_VENDOR by LIFNR, sometimes helps in the performance when you sort by the key of your WHERE clause.

Regards,

Rich Heilman

0 Kudos

After the sort, you could also:


DELETE ADJACENT DUPLICATES FROM it_vendor COMPARING lifnr.

Rob