04-04-2006 2:24 AM
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.
04-04-2006 2:35 AM
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
04-04-2006 2:35 AM
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
04-04-2006 2:43 AM
Hi Rich,
So your eman to say that the loop is not required?Thanks a lot for your help!
04-04-2006 2:46 AM
04-04-2006 2:53 AM
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!
04-04-2006 3:00 AM
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
04-04-2006 3:01 AM
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
04-04-2006 3:53 AM
After the sort, you could also:
DELETE ADJACENT DUPLICATES FROM it_vendor COMPARING lifnr.
Rob