Time out error while fetching records from table BKPF

[Former Member]

Hi,

I am fetching records from table BKPF using BUKRS & AWKEY in where clause. Query is as follows:

SELECT BELNR XBLNR AWKEY

FROM BKPF

INTO TABLE L_I_BKPF_TEMP

PACKAGE SIZE 500

WHERE BUKRS LIKE L_C_EG

AND AWKEY IN L_R_AWKEY .

APPEND LINES OF L_I_BKPF_TEMP TO I_BKPF .

ENDSELECT .

Program is giving time out error. There are 25628 records in range L_R_AWKEY , i m fetching 500 records at a time using PACKAGE SIZE. But the execution of prog stops on this query.

Please suggest something to overcome this problem.

[Former Member]

Hi,

I am fetching records from table BKPF using BUKRS & AWKEY in where clause. Query is as follows:

SELECT BELNR XBLNR AWKEY

FROM BKPF

INTO TABLE L_I_BKPF_TEMP

PACKAGE SIZE 500

WHERE BUKRS LIKE L_C_EG

AND AWKEY IN L_R_AWKEY .

APPEND LINES OF L_I_BKPF_TEMP TO I_BKPF .

ENDSELECT .

Program is giving time out error. There are 25628 records in range L_R_AWKEY , i m fetching 500 records at a time using PACKAGE SIZE. But the execution of prog stops on this query.

Please suggest something to overcome this problem.

[Rui_Dantas]

You should make a trace to confirm that, but probably the DB is not using the index by AWKEY because that index (BKPF~4 in my system) starts with AWTYP.

Check if you can add AWTYP to the where clause (AWTYP says if what you have in AWKEY is an accounting document, a material document, etc, so you should be able to fill it).

Rui

[Former Member]

Hi

Rui is right,

if you need to get the data by operation parameters u have to use the fields AWTYP and AWKEY.

In this selection u can omit the company code.

SELECT BELNR XBLNR AWKEY FROM BKPF
       INTO TABLE L_I_BKPF_TEMP
             PACKAGE SIZE 500
                  WHERE   AWTYP = <......> "<------------
                         AND AWKEY IN L_R_AWKEY .
       APPEND LINES OF L_I_BKPF_TEMP TO I_BKPF .
ENDSELECT .

Max

[Former Member]

thanks for ur replies

but i can not use AWTYP while fetching as I didnt get value for that from table EKBE.

Can anybody help with any other solution.

[ThomasZloch]

AWTYP could be fixed value "RMRP" or "MKPF" maybe, give those a shot. You definitely want to include this in the WHERE-conditions for a chance to speed things up.

Thomas

[Rui_Dantas]

Hi,

Take some of the documents you know you want, and check the AWTYP you have there.

Is it always the same?

If you need more help, more detail is needed:

... how are you building your AWKEY range? from EKBE? with which field(s)? are you using one fixed VGABE?

Rui

[Former Member]

Try getting rid of the PACKAGE SIZE addition. It's only needed if you have memory problems and won't speed up the SELECT.

Rob

[Former Member]

Hi,

Points to perform:

1. Try to avoid SELECT and ENDSELECT.

2. Try to use %_HINTS statement to force table to hit specific index.

3. Avoid LIKE, it potentially slows down performance.

Try to write the code as below.

SELECT BELNR XBLNR AWKEY

FROM BKPF

INTO TABLE L_I_BKPF_TEMP

PACKAGE SIZE 500

WHERE BUKRS = L_C_EG "LIKE L_C_EG

AND AWKEY IN L_R_AWKEY

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

APPEND LINES OF L_I_BKPF_TEMP TO I_BKPF .

ENDSELECT .

Regards,

Venkat

[Former Member]

Venkat - it's generally a better idea to optimize code rather than trying to force an index that may or may not help performance.

Rob