Performance tuning for BSAD table

[Former Member]

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.

[Former Member]

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

[Former Member]

> 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

[Former Member]

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

[Jelena_Perfiljeva]

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 <b>all</b> the cleared documents for <b>all</b> 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

[Former Member]

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

Rob

[Former Member]

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

[Former Member]

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 <b>augdt EQ p_date</b> "Parameter: Clearing Date

AND <b>gjahr EQ w_gjahr</b>.

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'.

<b>

Reward with points if helpful.</b>

Regards,

Vijay

[Former Member]

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.