topic Re: Poor performance select from bsak in Application Development and Automation Discussions

Poor performance select from bsak

Former Member — Thu, 08 Feb 2007 13:33:23 GMT

Hello,

i need to select data first from BSAS this is processed quickly but the second select statement is very poor. But I dont know why because number of records are cca the same and indexes are on the same fields. Should I have remove "corresponding" statement?

SELECT bsas_{bukrs bsas}hkont bsas_{augbl bsas}zuonr bsas~gjahr
bsas_{belnr bsas}buzei bsas_{blart bsas}shkzg bsas~dmbtr
bsas_{wrbtr bsas}sgtxt bsas_{kostl bsas}pswsl bsas~pswbt
FROM bsas INNER JOIN bkpf
ON bsas_{bukrs = bkpf}bukrs
AND bsas_{augbl = bkpf}belnr
AND bsas_{augdt = bkpf}budat
INTO CORRESPONDING FIELDS OF TABLE i_bseg
WHERE bkpf~bukrs = p_bukrs
AND ( bkpf_{blart = c_ab OR bkpf}blart = c_sa )
AND bkpf~cpudt IN r_date
AND bkpf~cputm >= w_utime
AND bkpf~cputm <= w_utime1.

SELECT bsak_{bukrs bsak}lifnr bsak_{augbl bsak}zuonr
bsak_{gjahr bsak}belnr bsak_{buzei bsak}blart
bsak_{shkzg bsak}dmbtr bsak_{wrbtr bsak}sgtxt bsak~hkont
bsak_{kostl bsak}xref2 bsak_{pswbt bsak}pswsl
FROM bsak INNER JOIN bkpf
ON bsak_{bukrs = bkpf}bukrs
AND bsak_{augbl = bkpf}belnr
AND bsak_{augdt = bkpf}budat
APPENDING corresponding fields of table i_bseg
WHERE bkpf~bukrs = p_bukrs
AND ( bkpf_{blart = c_ab OR bkpf}blart = c_sa )
AND bkpf~cpudt IN r_date
AND bkpf~cputm >= w_utime
AND bkpf~cputm <= w_utime1.

Re: Poor performance select from bsak

rahulkavuri — Thu, 08 Feb 2007 13:36:47 GMT

hi is ur internal table structure exactly same as ur selection fields, in that case u remove the corresponding addition there

Suppose there is an internal table like this

data: begin of itab,

matnr like mara-matnr,

mtart like mara-mtart,

end of itab.

now if use

select * from mara into corresponding fields of itab.

This will search all the field names in MARA to match strcture of itab. this detiorates performance

reward points if found helpful

Re: Poor performance select from bsak

Former Member — Thu, 08 Feb 2007 13:39:25 GMT

Hi,

IF internal table structure is not exactly same as your selection fields then make it according to selection fields as written in select statement & replace INTO CORRESPONDING FIELDS OF TABLE i_bseg

with INTO TABLE i_bseg.

Ashven

Re: Poor performance select from bsak

Former Member — Thu, 08 Feb 2007 14:37:21 GMT

the secondary index tables (BSAS and BSAK) are really only meant to be used when you know the GL account or vendor, which in this case you don't know.

I would re-write the SELECTS to first go against BKPF and then use FOR ALL ENTRIES to go against BSEG.

Yout SELECT against BKPF doesn't use the index very well, but if you check out this, it will show you how to speed that up as well:

/people/rob.burbank/blog/2006/09/13/using-an-index-when-you-dont-have-all-of-the-fields

Rob

Re: Poor performance select from bsak

Former Member — Thu, 08 Feb 2007 15:11:46 GMT

Hi Triska,

Since you have BUKRS and BLART (C_ab, c_sa....) along with all other possible parameters,

Write a query seperately on BKPF,

and after that use BSAS & BSAK seperately, for all entries in t_bkpf..

Hope this will help

Regards,

Message was edited by:

MANJUNATHA KS