topic Re: Performance tuning for BSAD table in Application Development and Automation Discussions

Performance tuning for BSAD table

Former Member — Mon, 10 Sep 2007 10:49:14 GMT

Hi All,

How can I improve performance for this select query?I have only 'Clearing Date' on my selection screen.So I can use only 1 table key out of 10 keys..please suggest if I can use BSEG table or not?

SELECT augdt "Clearing Date

vbeln "Billing Document

FROM bsad

INTO TABLE it_bseg

WHERE augdt EQ p_date "Parameter: Clearing Date

AND bschl IN ('01' , '02' , '11' , '12')

AND vbeln NE space.

Re: Performance tuning for BSAD table

Former Member — Mon, 10 Sep 2007 11:32:10 GMT

As you mentioned you can use only one key out of the following

MANDT

BUKRS

KUNNR

UMSKS

UMSKZ

AUGDT

AUGBL

ZUONR

GJAHR

BELNR

BUZEI

Please try to avoid using the non-key fields in the selection.

Soon after the selection is over, you can restrict your internal table as you wish.

Try to give more key fields so that your performance will be better.

Regards,

Baburaj

Re: Performance tuning for BSAD table

Former Member — Mon, 10 Sep 2007 12:55:39 GMT

> Please try to avoid using the non-key fields in the selection.

This is no official performance recommendation! You should always specify all your knowledge in the where condition to select only what you really need.

But you must try to specify also fields which are in an index and which are selective, otherwise the statement will be slow.

New indexes are only an option if the statement is very important.

Siegfried

Re: Performance tuning for BSAD table

Former Member — Mon, 10 Sep 2007 13:58:09 GMT

I suspect you're going about this the wrong way. Instead of going through FI (BSAD) to get the billing document. I think you may be able to use SD document flow (VBFA) to get it.

Rob

Re: Performance tuning for BSAD table

Jelena_Perfiljeva — Thu, 20 Sep 2007 20:05:39 GMT

VBFA is not going to help much because it does not have entries for FI documents.

It seems that author is trying to get a list of the cleared billing documents. I would suggest to check with the users if they would agree to minimize the search range. I seriously doubt that anyone is planning to look at all the cleared documents for all companies. Most likely the list may be narrowed down by Sales Org (VKORG) or at least Company (BUKRS).

Depending on that, the author might want to start search from the SD tables (VBRK, for example) and then join BSAD using BUKRS and KUNNR.

Message was edited by:

Jelena Perfiljeva

Re: Performance tuning for BSAD table

Former Member — Thu, 20 Sep 2007 20:09:56 GMT

Using AWTYPE and AWKEY, I think it can be done.

Rob

Re: Performance tuning for BSAD table

Former Member — Fri, 21 Sep 2007 07:27:45 GMT

Hi,

you do the following syt which is not give 100% performance but it gives good than ur select statment.

Any way you have the CLREARING date from that u need to pick the physical year and move into one field say w_gjahr.

w_gjahr = p_date +0(4).

SELECT augdt "Clearing Date

vbeln "Billing Document

FROM bsad

INTO TABLE it_bseg

WHERE augdt EQ p_date "Parameter: Clearing Date

AND gjahr EQ w_gjahr.

if sy-subrc = 0.

sort it_bseg by bukrs.

endif.

DELETE it_bseg where vbeln eq space

and bschl eq '01'

and bschl eq '02'

and bschl eq '11'

and bschl eq '12'.

Reward with points if helpful.

Regards,

Vijay

Re: Performance tuning for BSAD table

Former Member — Fri, 21 Sep 2007 13:47:10 GMT

1) I am not sure what you are doing here. You are reading data from table BSAD and are storing it in internal table IT_BSEG. Though programmatically it will not cause a problem but it severly affects readability. If I read internal table IT_BSEG I assume that it contains data from table BSEG. It would surprise me if it contained data from BSAD.

2) You do not have much to access BSAD with. Unless you create an index on field AUGDT and BSCHL you are pretty much doing a whole table search. There are no standard indexes (in version 4.7) that you are likely to use. There is an index in version 4.7 (Index No. 5) on fields BUKRS, BELNR, GJAHR. If you have already retrieved data from BKPF or BSEG you can use that internal table in the FOR ALL ENTRIES clause and mention the BUKRS, BELNR and GJAHR in the where clause. That way you will be using an index instead of a whole table search.

Plerase let me know if this helps.

Re: Performance tuning for BSAD table

Former Member — Mon, 01 Oct 2007 14:02:09 GMT

How exactly it can be done using AWKEY and AWTYPE..can you elaborate?