DTP taking long time

[former_member566306]

Hi ,

I am loading data from PSA to DSO .In the transformation we have written four field routines.

For 3 info objects we are fetching data from Billing DSO with the following code.But its taking long time to for loading.

Billing DSO table has 552394 records target DSO Condition data has around 800000 records.

Condition DSO has around 1600 Data packages.

1.

data netval_inv1 type tys_TG_1-netval_inv.

select single netval_inv from /bic/ads_bil00 into netval_inv1 where

bill_num = SOURCE_FIELDS-VBELN and bill_item = SOURCE_FIELDS-POSNR.

if sy-subrc = 0.

RESULT = netval_inv1.

endif.

2.

data stor_loc type tys_TG_1-STOR_LOC.

select single stor_loc from /bic/ads_bil00 into stor_loc where bill_num

= SOURCE_FIELDS-VBELN and bill_item = SOURCE_FIELDS-POSNR.

RESULT = stor_loc.

3.

data GT_EXNUM1 type tys_TG_1-GT_EXNUM.

select single GT_EXNUM from /bic/ads_exdcs00 into GT_EXNUM1 where

/BIC/ZC_RDOC = SOURCE_FIELDS-VBELN .

RESULT = GT_EXNUM1.

4.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = SOURCE_FIELDS-BSTNK_VF

IMPORTING

OUTPUT = RESULT.

translate RESULT to upper case.

The processing time for a sample Data package is given below.

Data Package 1112 ( 5001 Data Records ) 11min 23 sec.

Extraction DataSource 2LIS_13_VDKON : 5001 Data Records 6min 57.

RSDS 2LIS_13_VDKON HDSCLNT130 -> ODSO 0SD_O06 : 5001 -> 5001 Data Records 4 min 25 sec.

Please give a solution to reduce the processing time.

Edited by: dharmatejandt on May 13, 2011 5:58 AM

[esjewett]

You've got SELECT statements in your field routines, with three SELECTs per record. Assuming that your package size is 30,000 records, with 1600 packages we are looking at 144,000,000 SELECTs being run against your database while this load is running, just for field routines. That is a lot.

You need to revamp your transformation to do your SELECTs in the start routine restricted to the values in the current package. These SELECTs should populate an internal table and then you should read from that internal table in the field routines to populate the result value (make sure your table key is correct in the internal table).

Better yet, redesign your BW architecture so that you can use master data lookup rule types. Or if you are using Netweaver 7.3, use the new DSO lookup rule type. These rule types invoke ABAP written by SAP that should be highly efficient.

The previous poster who noted that you have too many packages in this load was also correct, and you should do something to remedy that, but it is likely not your primary problem.

Cheers,

Ethan

[sven_mader2]

Hi,

>around 1600 Data packages

This is too much. Increase the size with the transfer rate in the infopackage.

=> you should have less than 1000 packages.

=> or, you have to wait.

Sven

[former_member566306]

Hi Sven Mader,

DTP is still under processing .Is it possible to make the changes what you have mentioned while DTP is under processing.Does the code what I have written effecting the performance ?

Edited by: dharmatejandt on May 13, 2011 8:39 AM

Edited by: dharmatejandt on May 13, 2011 8:40 AM

Edited by: dharmatejandt on May 13, 2011 8:50 AM

Edited by: dharmatejandt on May 13, 2011 8:51 AM

[former_member186445]

you should wait with any change to the objects, till the loads are finished or have been stopped.

to improve performance, use a start routine, you can combine the three selects in one select.

don't forget to refresh all your internal tables

M.

[mr_v]

Two ways u can improve DTP loads.

1. Prefer 'Start Routine' wherever possible, instead of field routine in transformation.

2. In DTP 'Extraction' tab, reduce package size , so that the DTP load share no.of processes.

(dbl click DTP, menu... GoTo --> Settings for batch Manager .. increase no.of Processes... this no. depends on no.of BGDs in

SM50)

[former_member566306]

Thanks for your reply,

DTP is still under processing.Can I make the changes what you have given while DTP is under processing.