BSAD Performance Acces

[former_member686620]

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.

[former_member686620]

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.

[Former Member]

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

[Former Member]

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.

[former_member686620]

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.

[former_member226225]

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.

[kiran_k8]

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

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

K.Kiran.

[Former Member]

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

[JuanCarlosDelga]

Hi Alejandro,

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

Regards,

JCD

[former_member686620]

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.

[former_member226225]

Hi Alejandro,

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

Thanks & Regards,

Raghunadh Kodali.

[former_member686620]

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.

[former_member226225]

Thank you very much Alejandro for sharing....