Problem in Function Module(fetching data taking long time)

[Former Member]

Hi,

It's a Very starnge problem.We are trying to extract data(using Function Module) from a source database table XXXX which has millions of records to BW.The Problem is FM takes 3-4 hours to extract the records.The Code is:

if not l_r_tdname is initial.

OPEN CURSOR WITH HOLD S_CURSOR FOR

SELECT TDOBJECT TDNAME TDID TDSPRAS TDLDATE

FROM XXXX

WHERE TDNAME IN L_R_TDNAME

and TDOBJECT EQ 'VBBK'

and TDID EQ 'Z139'

and TDSPRAS EQ 'EN'.

else.

• Determine number of database records to be read per FETCH statement

• from input parameter I_MAXSIZE.

OPEN CURSOR WITH HOLD S_CURSOR FOR

SELECT TDOBJECT TDNAME TDID TDSPRAS TDLDATE

FROM XXXX

WHERE TDLDATE IN L_R_TDLDATE

and TDOBJECT EQ 'VBBK'

and TDID EQ 'Z139'

and TDSPRAS EQ 'EN'

and ( TDMACODE1 EQ 'VA01SAPMV45A' OR

TDMACODE1 EQ 'VA02SAPMV45A' ).

endif.

ENDIF. "First data package ?

• Fetch records into interface table.

• named E_T_'Name of extract structure'.

FETCH NEXT CURSOR S_CURSOR

APPENDING CORRESPONDING FIELDS

OF TABLE E_T_DATA

PACKAGE SIZE S_S_IF-MAXSIZE.

IF SY-SUBRC 0.

CLOSE CURSOR S_CURSOR.

RAISE NO_MORE_DATA.

ENDIF.

• once the deltas have been retrieved use to populate FM parameters.

loop at e_t_data.

• Site id is Z139, language is always EN

• text name is the sales order number and the object is VBBK

CALL FUNCTION 'READ_TEXT'

EXPORTING

ID = e_t_data-tdid " Site type

LANGUAGE = e_t_data-tdspras " language

NAME = e_t_data-tdname " sales order

OBJECT = e_t_data-tdobject " text object

• IMPORTING

• HEADER =

TABLES

LINES = it_tline

• EXCEPTIONS

• ID = 1

• LANGUAGE = 2

• NAME = 3

• NOT_FOUND = 4

• OBJECT = 5

• REFERENCE_CHECK = 6

• WRONG_ACCESS_TO_ARCHIVE = 7

• OTHERS = 8

.

IF SY-SUBRC 0.

• MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

• WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

As you see in the FM,

Problem-1->

In the beginning of FM,data is fetched from database table XXXX and loaded to CURSOR and then to an internal table E_T_DATA.It takes lot of time(2 hours e.g for 1 million records).Now if during this time(while fetching of data happens) some user deletes some entries in database table XXXX.Then the data records of internal table E_T_DATA and database table XXXX (for same selection) does not match.So it gives an error. Can you please provide any solution for this so that we may stop error?

Problem-2>

Can anyone provide a modified code to reduce the data fetching time from data base XXXX?

I will really be thankful for the responses and provide proper credit.

[Former Member]

You've left out the most important bit of information. What table is XXXX?

Rob

[Former Member]

Hi Rob,this table XXXX is a database table and have millions of records...lot of transactional data!!

[Former Member]

I know it's a database table, but what is the actual name. This is important to figure out if an index is being used.

Rob

[Former Member]

Database table is :STXH

[Former Member]

If l_r_tdname is empty, you will not be able to use the index very well.

Rob

[vinod_vemuru2]

Hi,

Why this open cursor, close cursor???

Why can't u directly try with PACKAGE SIZE option of select ???

Also INTO CORROSPONDING FIELDS!!!!

For data consistency u have to lock the table before select and unlock it after select.

What table u r using? Check if there are any indexes available.

If it is one time upload then 4hrs wont be a problem(I hope it is RFC).

Also check if there is any FM which does the trick for u to fetch the data!

Thanks,

Vinod.

[Former Member]

hi Vinod,

This is not one time pick of records..this load we have to do daily!!

[vinod_vemuru2]

Hi,

Check this select carefully. It bypasses primary INDEX usage

Because ur where clause fields are not in the same order as they appear in the table.

OPEN CURSOR WITH HOLD S_CURSOR FOR

SELECT TDOBJECT TDNAME TDID TDSPRAS TDLDATE

FROM XXXX

WHERE TDNAME IN L_R_TDNAME

and TDOBJECT EQ 'VBBK'

and TDID EQ 'Z139'

and TDSPRAS EQ 'EN'.

WHERE TDOBJECT EQ 'VBBK'

and TDNAME IN L_R_TDNAME

and TDID EQ 'Z139'

and TDSPRAS EQ 'EN'.

Thanks,

Vinod.