Data load takes long time in DSO when look up dso changes to write optimized type

[Former Member]

Hi All,

We have a delta  load from DSO 1 to DSO2 picking 30k records .It has end routine which  does a look up on standard DSO (say DSO3).having 26 million records

The load DSO 1 to DSO2 takes few minutes to finish .But after changing the look up DSO3 to write optimized type, the load from DSO1 to DSO2 taking more than 2 hours .

Can you please suggest why it takes much take after changing the dso type.

We changed to write optimized because standard dso takes long time for activation step.,

Regards

Pradeep

[Former Member · Accepted solution]

You can have a key field in standard DSO but in case of wite optimized DSO there are technical key. They are Request GUID field (0REQUEST), the Data Package field (0DATAPAKID) and the Data Record Number field (0RECORD).

Hence you can create semantic key on DOC NUMBER and sorting the key set and applying Binary search will suffice your problem.

[fcorodriguezl]

Hi Pradeep,

You need optimize your final routine, if your lookup have all key of ods3, there should be no problem, check your sort instruction and read. If you not have all keys, you need create a index in your ods3.

Please share your code, maybe can help.

Regards.

[Former Member]

Hi Francis,

Here is the code

SELECT　DISTINCT　DOC_NUMBER　ZPARV　EMPLOYEE　INTO　CORRESPONDING
FIELDS　OF　TABLEIT_ZSD_DSO1

　FROM　/BIC/ADSO1

　FOR　ALL　ENTRIES　IN　SOURCE_PACKAGE

WHERE　DOC_NUMBER　=　SOURCE_PACKAGE-DOC_NUMBER

AND　ZPARV　IN　('ZJ','KB').

In look up dso, EMPLOYEE is in Data field and the same was working fine when it was standard type

Regards

Pradeep

[fcorodriguezl]

Hi!

Try this:

SELECT doc_number

               zparv

               employee

INTO TABLE it_zsd_ds01

FOR ALL ENTRIES IN source_package

WHERE doc_number = source_package-doc_number AND

               zparv IN ('ZJ', 'KB').

IF sy-subrc EQ 0.

    SORT it_zsd_ds01 BY doc_number zparv employee.

     DELETE ADJACENT DUPLICATES FROM it_zsd_ds01 COMPARING doc_number zparv      employee

ENDIF.

LOOP source_package ASSIGNING <source_fields>.

  READ TABLE it_zsd_ds01 INTO wa_zsd_ds01

                         WITH KEY doc_number = <source_fields>-doc_number

                                                       zparv = <source_fields>-zparv

  BINARY SEARCH.

  IF sy-subrc EQ 0.

          "your assign, for example.

          <source_fields>-employee = wa_zsd_ds01-employee.

  ENDIF.

ENDLOOP.

Regards.

[Former Member]

Hi Francis,

Issue  got resolved after creating sec index for DOC_NUMBER　ZPARV　EMPLOYEE .

But when it was standard type ,no sec index was created .

Regards

[fcorodriguezl]

Hi Pradeep,

If you want decrease the time, you can create other standard DSO or a infoobject with your doc_number zparv and employee where your zparv in 'ZB' and 'KB'.

Your can use this object in initial routine or your you can use read masterdata.

I hope helps.

Regards.

[former_member197660]

Hi Pradeep

The end routine for lookup on DSO containing 28 Mio records is certainly going to run long.

Please try to optimize the lookup code by making precise selections in data read statement and try to create secondary indexes on write optimized DSO based on the fields you use for selections.

This will improve data read time from WO DSO.

However please mention the runtime of data load when you were trying to make a lookup on Standard DSO.

Ashish

[Former Member]

Hi Ashish,

How to find runtime of data load when trying to make a lookup on Standard DSO.

But the same look up code  takes less time when it was standard dso

Regards

Pradeep

[Former Member]

In case of standard DSO there is a concept of SID. All the data present in the Satndard DSO has a sid corresponding to every data in the DSO and hence look up is faster.

In case of Write Optimized DSO there are no SID's concept. So look up involves actual values looked up. It is always recommended to have a standard DSO on top of a Write Optimized DSO.

Refer for more details.

[Former Member]

HI Raja,

I think SID is for reporting purpose.Does SID support for look up as well?

Regards

Pradeep