10-13-2006 2:19 AM
Hi,
I have a issue, need to improve the performance on this report which uses inner join
SELECT bsisbukrs bsisbelnr bsisgjahr bsisblart bsis~bldat
bsisbudat bsisxblnr bkpfbvorg bkpfbstat bkpf~waers
bkpfhwaer bkpfhwae2 bsisbuzei bsisaugdt bsis~augbl
bsisshkzg bsisdmbtr bsiswrbtr bsisdmbe2 bsis~sgtxt
bsiskostl bsisaufnr bsishkont bsisprctr bkpf~cpudt
bsiszztitle bsiszzterr
FROM bsis
JOIN bkpf
ON bkpfbukrs EQ bsisbukrs
AND bkpfbelnr EQ bsisbelnr
AND bkpfgjahr EQ bsisgjahr
INTO CORRESPONDING FIELDS OF t_data
WHERE bsis~bukrs IN so_bukrs
AND bsis~hkont IN so_hkont
AND bsis~budat IN so_budat
AND bsis~augdt IN so_augdt
AND bsis~gjahr IN so_gjahr
AND bsis~belnr IN so_belnr
AND bsis~blart IN so_blart
AND bsis~xblnr IN so_xblnr
AND bsis~bldat IN so_bldat
AND bsis~zztitle IN so_title
AND bsis~zzterr IN so_terr
AND bsis~kostl IN so_kostl
AND bsis~prctr IN so_prctr
AND bsis~aufnr IN so_aufnr
AND bkpf~cpudt IN so_cpudt
AND bkpf~bvorg IN so_bvorg.
I ran the report in BGD from morning still it is running.
Could any one help me how to change the code instead of using innerjoin .
I checked in sm51 ,says sequential read on BSIS it continues...forever...
Please suggest.
Thanks
Chary
10-13-2006 2:23 AM
hi chary the select sequence of fields should be according to the table
just like this below...
SELECT bsisbukrs bsisgjahr bsisbelnr bsisbudat bsisbldat bsisxblnr bsis~blart .......... and so on
so change the sequence.. and try
<b>IS UR PROBLEM SOLVED? IF NOT PLZ LET ME KNOW</b>
Regards
- Gopi
Message was edited by: Gopi Narendra
10-13-2006 2:41 AM
hi gopi,
Ok I will try,
Is there any other option instead of innerjoins.
thanks
10-13-2006 3:25 AM
Hi Chary,
Try this
SELECT
bukrs
hkont
augdt
augbl
zuonr
gjahr
belnr
buzei
budat
bldat
WAERS
xblnr
blart
shkzg
dmbtr
wrbtr
sgtxt
aufnr
kostl
dmbe2
prctr
zztitle
zzterr
FROM bsis
into table ITAB_BSIS "Declare an Internal table with
"all above selected fields
WHERE bukrs IN so_bukrs
AND hkont = so_hkont
AND augdt = so_augdt
AND gjahr = so_gjahr
AND belnr = so_belnr
AND budat = so_budat
AND blart = so_blart
AND xblnr = so_xblnr
AND bldat = so_bldat
AND zztitle = so_title
AND zzterr = so_terr
AND kostl = so_kostl
AND prctr = so_prctr
AND aufnr = so_aufnr.
if not ITAB_BSIS[] is initial.
select BUKRS BELNR GJAHR bvorg bstat waers hwaer hwae2 cpudt
from bkpf
into table ITAB_BKPF "Declare an Internal table with
"all above selected fields
for all entries in ITAB_BSIS
where bukrs = ITAB_BSIS-bukrs
and belnr = ITAB_BSIS-belnr
and gjahr = ITAB_BSIS-gjahr
AND cpudt = so_cpudt
AND bvorg = so_bvorg.
endif.
Hope this will resolve your problem.
Please assign points if the above solution is help full to you.
Regards,
Mohammad
Message was edited by: SAP User
Message was edited by: SAP User
10-13-2006 3:43 AM
If so_hkont is empty, it will have to read the entire table (BSIS). Try it with a small range. I think that'll be pretty quick.
Rob
PS - you'll probably get more response if you mark this as a question.
Message was edited by: Rob Burbank
10-13-2006 11:01 PM
Hi Mohammed,
Thanks for the solution, it made a lot of difference in the execution of the report.
Thanks a bunch!!!!
OK now how to assign points to all of you.
let me know
10-13-2006 9:22 AM
Hi
select bukrs belnr
from bkpf
into table i_bkpf "declare an internal table
where gjahr in so_gjahr.
Select bsis data based on i_bkpf.
Hope it helps