topic Re: Problem in Function Module(fetching data taking long time) in Application Development and Automation Discussions

Problem in Function Module(fetching data taking long time)

Former Member — Fri, 08 Aug 2008 14:43:34 GMT

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.

Re: Problem in Function Module(fetching data taking long time)

Former Member — Fri, 08 Aug 2008 15:02:02 GMT

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

Rob

Re: Problem in Function Module(fetching data taking long time)

vinod_vemuru2 — Fri, 08 Aug 2008 15:02:49 GMT

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.

Re: Problem in Function Module(fetching data taking long time)

Former Member — Fri, 08 Aug 2008 15:08:10 GMT

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

Re: Problem in Function Module(fetching data taking long time)

Former Member — Fri, 08 Aug 2008 15:09:07 GMT

hi Vinod,

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

Re: Problem in Function Module(fetching data taking long time)

Former Member — Fri, 08 Aug 2008 15:11:19 GMT

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

Re: Problem in Function Module(fetching data taking long time)

Former Member — Fri, 08 Aug 2008 15:15:19 GMT

Database table is :STXH

Re: Problem in Function Module(fetching data taking long time)

Former Member — Fri, 08 Aug 2008 15:19:16 GMT

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

Rob

Re: Problem in Function Module(fetching data taking long time)

vinod_vemuru2 — Sat, 09 Aug 2008 11:57:31 GMT

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.