topic Re: BSAD Performance Acces in Application Development and Automation Discussions

BSAD Performance Acces

former_member686620 — Sun, 02 Nov 2014 00:25:35 GMT

Hello everybody,

I am fetching around 2.5 million records from BKPF and afterwards I must to read BSAD, but it's taking around 2 hours to access the table and ends in a time out dump.

What can i do? I made a secondary index in BSAD because in don't have the full key, but the problem persist. I tried with FOR ALL ENTRIES and INNER but i don`t have good results.

Please help.

These are an example of my last queries:

SELECT bukrs gjahr belnr budat bktxt blart kursf hwaer

INTO TABLE gt_bkpf

FROM bkpf

WHERE bukrs = '2001'

AND gjahr = 2011

AND monat = 12

AND bstat = ' '

%_HINTS ORACLE 'INDEX("BKPF~ZBK")'. "ORACLE.

SELECT a~bukrs a~belnr a~gjahr a~buzei a~hkont a~kunnr a~sgtxt a~bschl a~shkzg

a~dmbtr a~hbkid

INTO TABLE gt_bsad

FROM bsad AS a

INNER JOIN bkpf AS b

ON a~bukrs = b~bukrs

AND a~belnr = b~belnr

AND a~gjahr = b~gjahr

%_HINTS ORACLE 'INDEX("BSAD~ZBS")'. "ORACLE.

Re: BSAD Performance Acces

Former Member — Sun, 02 Nov 2014 04:37:48 GMT

BSAD is an Index table, to be used to for accessing cleared items for customers, since you donot have customer in your query and you are accessing via BUKRS, BELNR, GJAHR so BSEG should be more appropriate, but you 'll need additional conditions to restrict to cleared items, your functional consultant should be able to help with this.

you may also check the FM GET_CLEARED_ITEMS if it gives you accurate data you are looking for

Re: BSAD Performance Acces

Former Member — Sun, 02 Nov 2014 04:46:52 GMT

Just noticed the code details.

you shouldn't be using Inner Join,

Use as follows:


SELECT bukrs gjahr belnr budat bktxt blart kursf hwaer
   INTO TABLE gt_bkpf
   FROM bkpf
   WHERE bukrs = '2001'
   AND gjahr = 2011
   AND monat = 12
   AND bstat = ' '
   %_HINTS ORACLE 'INDEX("BKPF~ZBK")'. "ORACLE.

if gt_bkpf is not initial.
SELECT bukrs belnr gjahr buzei hkont kunnr sgtxt bschl shkzg
dmbtr hbkid
INTO TABLE gt_bsad
FROM bsad
FOR ALL ENTRIES IN gt_bkpf
WHERE bukrs = gt_bkpf-bukrs
and belnr = gt_bkpf-belnr
and gjahr = gt_bkpf-gjahr.

endif.

Your query doesnt have a Where condition it will fetch all entries from DB, Inner join has a entirely different purpose. Read about it.

Re: BSAD Performance Acces

former_member686620 — Sun, 02 Nov 2014 05:49:09 GMT

Thanks Kartik,

You're right... i forgot to put my WHERE condition.... I was using BSEG table with key BUKRS, BELNR, GJAHR and my previous one query was like this:

SELECT bukrs gjahr belnr budat bktxt blart kursf hwaer

INTO TABLE gt_bkpf

FROM bkpf

WHERE bukrs = '2001'

AND gjahr = 2011

AND monat = 12

AND bstat = ' '

%_HINTS ORACLE 'INDEX("BKPF~ZBK")'. "ORACLE.

IF gt_bkpf[] IS NOT INITIAL.

SELECT bukrs belnr gjahr buzei hkont lifnr kunnr sgtxt bschl shkzg

dmbtr hbkid

INTO TABLE gt_bseg

FROM bseg

FOR ALL ENTRIES IN gt_bkpf

WHERE bukrs = gt_bkpf-bukrs

AND belnr = gt_bkpf-belnr

AND gjahr = gt_bkpf-gjahr.

ENDIF.

But it was taking around 2 hours and it ends in time out dump, that's why I decided to use index tables like BSID, BSAD, BSIK, BSAK, BSIS and BSAS, but i was having problems with BSAD... I'm going to fix my query and let you know.

Re: BSAD Performance Acces

former_member226225 — Sun, 02 Nov 2014 14:52:04 GMT

Hi ALejandro ,

Get the GL Accounts which are required and harcode in your program,

You can go for BSAS table to get the the Accounting: Secondary Index for G/L Accounts (Cleared Items) and pass the Company Code , GLAccount , GJAHR , BELNR and verify the clearing documents AUGBL and clearing date AUGDT. So by going to the BSAS table may be it might improve the performance but check the data carefully.

I hope this will be helpful.

Thanks & Regards,

Raghunadh Kodali.

Re: BSAD Performance Acces

kiran_k8 — Mon, 03 Nov 2014 12:26:30 GMT

%_HINTS ORACLE 'INDEX("BKPF~ZBK")'. "ORACLE

As far as I know we shouldn't force an INDEX through select query.

K.Kiran.

Re: BSAD Performance Acces

JuanCarlosDelga — Mon, 03 Nov 2014 12:50:20 GMT

Hi Alejandro,

Why don't you use a cursor? Just divide & conquer.

Regards,

JCD

Re: BSAD Performance Acces

Former Member — Mon, 03 Nov 2014 13:13:48 GMT

This line wont have any affect, SAP optimizer will still pick up best suited index

Re: BSAD Performance Acces

former_member686620 — Mon, 03 Nov 2014 16:32:08 GMT

Hi Juan Carlos,

I solved as you said. I used CURSOR and now the query is taking a few minutes to fetch more than 2.5 million of records.

Thank to all for your time and help.

Greetings.

Alejandro.

Re: BSAD Performance Acces

former_member226225 — Mon, 03 Nov 2014 16:39:58 GMT

Hi Alejandro,

Can you post the code it will be helpful for me.

Thanks & Regards,

Raghunadh Kodali.

Re: BSAD Performance Acces

former_member686620 — Mon, 03 Nov 2014 17:51:06 GMT

Hello Raghunadh,

I share you my code. It's something like that:

DATA: l_c1 TYPE cursor.

IF gt_bkpf[] IS NOT INITIAL.

OPEN CURSOR l_c1 FOR SELECT bukrs belnr gjahr buzei hkont kunnr sgtxt bschl shkzg dmbtr hbkid

FROM bsad

FOR ALL ENTRIES IN gt_bkpf

WHERE bukrs = gt_bkpf-bukrs

AND belnr = gt_bkpf-belnr

AND gjahr = gt_bkpf-gjahr.

ENDIF.

WHILE l_c1 IS NOT INITIAL.

FETCH NEXT CURSOR l_c1

INTO TABLE gt_bsad PACKAGE SIZE p_pckg.

IF sy-subrc = 0.

" Do that you need.

ELSE.

CLOSE CURSOR l_c1.

ENDIF.

ENDWHILE.

I hope it help you.

- Alejandro.

Re: BSAD Performance Acces

former_member226225 — Tue, 04 Nov 2014 05:45:12 GMT

Thank you very much Alejandro for sharing....