Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Problem in Function Module(fetching data taking long time)

Former Member
0 Kudos
872

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.

1 ACCEPTED SOLUTION

vinod_vemuru2
Active Contributor
0 Kudos
257

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.

8 REPLIES 8

Former Member
0 Kudos
257

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

Rob

0 Kudos
257

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

0 Kudos
257

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

0 Kudos
257

Database table is :STXH

0 Kudos
257

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

Rob

vinod_vemuru2
Active Contributor
0 Kudos
257

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.

0 Kudos
257

hi Vinod,

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

vinod_vemuru2
Active Contributor
0 Kudos
258

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.