SQL statment running too long in a start routine

[Former Member]

Hello Experts,

Please help me here, I need your help and advice in solving this issue.

I am loading data into a cube using DTP and in the start routine I have a select statement where I read an ODS as a lookup. I am getting all records from ODS where the Material in the data package matches the material in the ODS.

The DTP is running too long, each data packet is 50,000 records and the ODS has around 1.6 million records. when I look at the DTP load monitor it shows that for each package the start routine is taking 30 mins to an hour and the only reason can be the SQL statment.

So How can I reduce this time on the Database read to the ODS, any advice would be appreciatted...

Thanks,

Swaroop

Here is the code in the start routine....I am also deleting any duplicate materials before selecting from the ODS

t_mat[] = SOURCE_PACKAGE[].

SORT t_mat by material_num.

Delete adjacent duplicates from t_mat

comparing material_num.

select MATERIAL

/bic/zdsp_lvl

/bic/zdsp_lvl0

/bic/zdsp_comp

/BIC/ZDSPCPQTY

FROM /BIC/AZ_DSO05900

into table t_bomdet

for all entries in t_mat

where MATERIAL = t_mat-material_num.

[Former Member]

Hello Experts,

Please help me here, I need your help and advice in solving this issue.

I am loading data into a cube using DTP and in the start routine I have a select statement where I read an ODS as a lookup. I am getting all records from ODS where the Material in the data package matches the material in the ODS.

The DTP is running too long, each data packet is 50,000 records and the ODS has around 1.6 million records. when I look at the DTP load monitor it shows that for each package the start routine is taking 30 mins to an hour and the only reason can be the SQL statment.

So How can I reduce this time on the Database read to the ODS, any advice would be appreciatted...

Thanks,

Swaroop

Here is the code in the start routine....I am also deleting any duplicate materials before selecting from the ODS

t_mat[] = SOURCE_PACKAGE[].

SORT t_mat by material_num.

Delete adjacent duplicates from t_mat

comparing material_num.

select MATERIAL

/bic/zdsp_lvl

/bic/zdsp_lvl0

/bic/zdsp_comp

/BIC/ZDSPCPQTY

FROM /BIC/AZ_DSO05900

into table t_bomdet

for all entries in t_mat

where MATERIAL = t_mat-material_num.

[Former Member]

Hello Swaroop,

maybe you can use a fetch in the select statement for reduce data_package size.

Your code could be this:

STATICS: s_cursor TYPE cursor.

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT MATERIAL

/bic/zdsp_lvl

/bic/zdsp_lvl0

/bic/zdsp_comp

/BIC/ZDSPCPQTY

FROM /BIC/AZ_DSO05900

into table t_bomdet

for all entries in t_mat

where MATERIAL = t_mat-material_num.

FETCH NEXT CURSOR s_cursor

INTO CORRESPONDING FIELDS

OF table t_bomdet.

IF sy-subrc <> 0.

CLOSE CURSOR s_cursor

ENDIF.

Hope it helps.

Best regards.

Simone.

[Former Member]

Thanks for your response Simone.

I don't think reducing the package size will help that much, the problem here is the reads to the database table but I will give this a try and see what happens.

Forum points awarded.

[Former Member]

Hi Swaroop Chandra,

For this case the cursor alone may not solve the issue as the parameters changes from each data package...

Questions:

For table /BIC/AZ_DSO05900, material is really unique?

If yes, the best shot is create an index on /BIC/AZ_DSO05900-material_num

Please reply as we can continue the discussions.

Regards, Fernando Da Rós

-

-

---

Edited by: Fernando Ros on Jul 26, 2011 2:34 PM

Now I've realized that this DSO is for BOM, so it's probably not unique, anyway the database access can gain a huge improvement on the access trough index.

But I'm not sure if in this case the pain point is only the DB access time. Did you traced?

Do you have some figures, like number of materials, number of executions, time of executions...

Please do a trace with ST30 or ST02

[Former Member]

Thanks for the response Fernando. You are exactly right, I am reading the BOM DSO while looping through my forecast and demand transaction data and for each material in my data package I am retrieving it's components so that my resulting cube has item-component realationship with demand and forecast data.

I did create an secondary index on the DSO table even though material is not unique and the loads are running very much faster now, the start routine which had the select statment is now taking seconds instead of minutes and the performance is much better.

But now the code is taking time when looping through the data package and then loop through the BOM data to establish item compoenent relationships but I don't think I can do anything else here to improve performance.

Is there anything else that I am missing?

Thanks again for the good answers, points awarded!

Swaroop

[Former Member]

Hi Swaroop,

You have other options also for optimize the loop of internal tables declaring it as SORTED with a non unique key for material, in this case the LOOP is automatic done like "binary search" so starting directly into the desired record and stopping loop when clause became false.

If you wish post your wasting time coding so we can be more precise on hints.

Regards, Fernando Da Ró

[matt]

This is really an ABAP question, so I've moved the thread to the ABAP performance forum. I hope that's ok - you're more likely to get specific answers!

For non-BI programmers, in BW you get start routines in transformations. Transformations map data from a source to a destination - transforming it along the way in various ways. A start routine takes the selected source data - provided in an internal table called SOURCE_PACKAGE - and allows the programmer to modify the contents of that internal table - changing fields, deleting/adding rows etc.

An ODS is a transparent table (technically). The poster has already added an appropriate index.

To the OP - have you tried grouping your source data using semantic groups? By grouping the data into - e.g. records with the same material - you can use SELECT INTO TABLE for an initial load of that material's data, thereafter interrogating an internal table, rather than the DSO. I.e. if the SOURCE_PACKAGE is suitably sorted which fields will have repeated values.

But Fernando is right - we need to see your full coding.

[Former Member]

Firstly, thanks so much Fernando, Matt and Simone for helping me out here, your advice and comments are great!

I am posting the rest of the code below, here is where I loop through my data package and then do a lookup on the BOM internal table which I built above to establish item-component relationships and then append all these new component records to the result package

 DATA: t_bomdet TYPE sorted TABLE OF TYPE_bomdet
                                         with non-unique key MATERIAL,
          w_bomdet LIKE LINE OF T_bomdet.


cons_pack[] = RESULT_PACKAGE[].

    SORT cons_pack by material

    Delete adjacent duplicates from cons_pack
    comparing material.


    LOOP AT cons_pack[] ASSIGNING <result_fields>.


      LOOP AT t_bomdet INTO w_bomdet
      WHERE material = <result_fields>-material.

        <result_fields>-/bic/zdsp_lvl     = w_bomdet-/bic/zdsp_lvl.
        <result_fields>-/bic/zdsp_lvl0    = w_bomdet-/bic/zdsp_lvl0.
        <result_fields>-/bic/zco_mat2     = w_bomdet-/bic/zdsp_component.


        Collect <result_fields> INTO temp_pack.

      ENDLOOP.

    ENDLOOP.

    APPEND LINES OF temp_pack TO RESULT_PACKAGE[].

[matt]

You've missed out your select, but I assume it would be

select MATERIAL

/bic/zdsp_lvl

/bic/zdsp_lvl0

/bic/zdsp_comp

/BIC/ZDSPCPQTY

FROM /BIC/AZ_DSO05900

into table t_bomdet

for all entries in cons_pack

where MATERIAL = cons_pack-material_num.

And it would go immediately after the DELETE ADJACENT DUPLICATES.

Add a DELETE t_bomdet, after LOOP AT t_bomdet WHERE... statement. Once you've read the record, you'll never need it again. cons_pack has only unique materials.

Given that both are sorted in the same way, you could probably get rid of the where clause entirely. But I doubt that it would save much, if any time.

Edited by: Matt on Aug 1, 2011 11:38 AM