Question Re: Start Routine Issue in Technology Q&A

Start Routine Issue

former_member184884 — Sat, 07 Sep 2013 02:18:59 GMT

Hi Mates,

We have are facing one strange issue in our production system we just fed up, i need all your valuable support in resolving this issue, Hope i will succeeded in this.

Scenario:

There is delta DTP which is part of daily process chain which will load the data from DSO(standard) to Cube as usual in the transformation level their is some business logic is written both in the start & end routine but this DTP will approximately 1.5 million records for this it will take

approx 10-14 hrs runtime,so after analysis we have found code which is written in the start routine is not optimized so we have done some minor changes in the code to improve the load performance.

But issue started from here onward we have done changes on development and transported to quality we have ran same DTP which is their in the production it's performance has improved drastically for 1.7 million records it took only 15 mins runtime so we felt very happy and on the same day we have transported code to production but big magic has happened here only.

when that particular DTP has ran in the production through schedule process chain it running for long time like for one data package(50000) to complete it is taking 3-4 hours so to pull all 1.5 million records it is running for 2 days due to this long run it has impacted entire production system means other loads.

So my doubt here is why it is take acting strange in the production, Please give your valuable reviews on my issue.

Below is the piece of code which is there is in the start routine

DATA : s_material TYPE /bi0/pmaterial ,

t_material LIKE STANDARD TABLE OF s_material,

wa_material LIKE LINE OF t_material,

s_matunit TYPE /bi0/pmat_unit,

t_matunit LIKE STANDARD TABLE OF s_matunit,

wa_matunit LIKE LINE OF t_matunit.

DATA : s_primrate TYPE /bic/azsd_o01900,

t_primrate LIKE STANDARD TABLE OF s_primrate,

wa_primrate LIKE LINE OF t_primrate,

min_calday LIKE sy-datum.

DATA : BEGIN OF r_compcode OCCURS 100,

sign TYPE c LENGTH 1,

option TYPE c LENGTH 2,

low TYPE c LENGTH 4,

high TYPE c LENGTH 4,

END OF r_compcode.

data : s_/BI0/PCOMP_CODE type /BI0/PCOMP_CODE ,

t_/BI0/PCOMP_CODE like STANDARD TABLE OF s_/BI0/PCOMP_CODE,

wa_/BI0/PCOMP_CODE LIKE LINE OF t_/BI0/PCOMP_CODE.

data wa_r_compcode like LINE OF r_compcode.

CLASS lcl_transform IMPLEMENTATION.

METHOD start_routine.

FIELD-SYMBOLS:

<SOURCE_FIELDS> TYPE _ty_s_SC_1.

DATA: MONITOR_REC TYPE rstmonitor.

min_calday = sy-datum - 30.

if SOURCE_PACKAGE is NOT INITIAL.

SELECT *

INTO TABLE t_material

FROM /bi0/pmaterial

FOR ALL ENTRIES IN SOURCE_PACKAGE

WHERE material eq SOURCE_PACKAGE-material

and objvers EQ 'A'.

SELECT *

INTO TABLE t_matunit

FROM /bi0/pmat_unit

FOR ALL ENTRIES IN SOURCE_PACKAGE

WHERE material eq SOURCE_PACKAGE-material

and objvers EQ 'A'.

endif.

select *

into table T_/BI0/PCOMP_CODE

from /BI0/PCOMP_CODE

where COUNTRY eq 'PK'

and OBJVERS eq 'A'.

loop at t_/BI0/PCOMP_CODE into wa_/BI0/PCOMP_CODE.

wa_r_compcode-sign = 'I'.

wa_r_compcode-option = 'EQ'.

wa_r_compcode-low = wa_/BI0/PCOMP_CODE-COMP_CODE.

wa_r_compcode-high = ''.

append wa_r_compcode to r_compcode.

endloop.

IF SOURCE_PACKAGE[] IS NOT INITIAL.

REFRESH: t_primrate.

DELETE SOURCE_PACKAGE[] WHERE comp_code NOT IN r_compcode.

SELECT *

INTO TABLE t_primrate

FROM /bic/azsd_o01900

FOR ALL ENTRIES IN SOURCE_PACKAGE

WHERE salesorg EQ SOURCE_PACKAGE-salesorg

and comp_code EQ SOURCE_PACKAGE-comp_code

and division EQ SOURCE_PACKAGE-division

and distr_chan EQ SOURCE_PACKAGE-distr_chan

and calday GT min_calday

and customer EQ SOURCE_PACKAGE-/bic/zsd_dist

and material EQ SOURCE_PACKAGE-material.

ENDIF.

SORT t_material BY material objvers.

SORT t_matunit BY material mat_unit objvers.

SORT t_primrate BY salesorg comp_code division

distr_chan calday customer material.

ENDMETHOD.

METHOD inverse_start_routine.

ENDMETHOD.

ENDCLASS.

Please free to ask me if you have any doubts regarding my issue.

Regards,

Harish

Re: Start Routine Issue

anshu_lilhori — Sat, 07 Sep 2013 08:12:39 GMT

Hi,

Their is lot of scope of improvement in you code i feel..Even though i am not an expert in ABAP but still can give you few pointers.

Instead of select * use select with the field name you need to select.Because select * picks all the fields from that particular table.

It would be good choice if you define your own structure with the required fields rather than accessing the complete table fields.

You can also check this out:Might be helpful and useful in improving performance of the code.

http://scn.sap.com/docs/DOC-45849

Regards,

Re: Start Routine Issue

MGrob — Sat, 07 Sep 2013 08:21:21 GMT

I would seek help it the ABAP Development space there is too many select * and loops across all material master. Most of us here aren't ABAP experts so I would try it over there

hope it helps

Martin

Re: Start Routine Issue

arindam_m — Sat, 07 Sep 2013 08:30:43 GMT

Hi,

The huge difference in the total entries in the tables where you are doing SELECT * could be the reason. My advise to look into applying JOINS instead of FOR ALL ENTRIES should help also instaed of * SELECT necessary fields only.

Cheers,

Arindam

Re: Start Routine Issue

anshu_lilhori — Sat, 07 Sep 2013 08:39:35 GMT

Hi,

Check out this wonderful document on performance tuning in BI ABAP

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/30190066-8d2d-2c10-718a-9b1b0a82972c?overridelayout=true

Hope this helps.

Regards,

Re: Start Routine Issue

KamalMehta — Sat, 07 Sep 2013 09:49:20 GMT

Hi Harish ,

There is really lot of scope of improvement in your code.

1. Use only required fields instead of select *. Doing this only you would see lot of performance improvement in your loading.

select *

into table T_/BI0/PCOMP_CODE

from /BI0/PCOMP_CODE

where COUNTRY eq 'PK'

and OBJVERS eq 'A'.

loop at t_/BI0/PCOMP_CODE into wa_/BI0/PCOMP_CODE.

wa_r_compcode-sign = 'I'.

wa_r_compcode-option = 'EQ'.

wa_r_compcode-low = wa_/BI0/PCOMP_CODE-COMP_CODE.

wa_r_compcode-high = ''.

append wa_r_compcode to r_compcode.

endloop.

You are only using COMP_CODE here to populate r_compcode so select COMP_CODE only in the select statement used above.

3. Also can you please let us know i see you are preparing lot of internal tables in start routine such as

t_material ,t_matunit ,t_primrate. Are you reading them in your field routines.

4. Does rest of the coding in your transformation end routine etc is working fine. Check those as well.

Thanks

Kamal

Re: Start Routine Issue

former_member184884 — Sat, 07 Sep 2013 12:36:45 GMT

Hi Anshu,

Thanks for your quick reply we are aware of their is so much scope to optimize my code but my question here is it is working fine in quality system why it is acting so strange when comes to production.

for your information we have checked version management and all those stuff but they are fine.

and even we have also checked in the source DSO is their any NULL or duplicate values are their but their is no as such entries.

Note: Source DSO(standard) and the lookup which i am doing is ZSD_O019 it is direct update DSO.

with my limited knowledge i am asking all you experts is their any DB level indexes inconsistency happened.......!!!! because we are suspecting that is their any chances due this we are facing this strange behavior in the production.

**i have already checked the link which you have shared and i asked my ABAP team to look into it but they told it doesn't make much sense by using DB join concepts and even it is not relevant to our scenario.

Please assist me how to resolve this.

Regards,

Harish

Re: Start Routine Issue

former_member184884 — Sat, 07 Sep 2013 12:58:41 GMT

Hi Arindam,

yes your are right the one which i am doing lookup zsd_o019 have huge number of entries because it contains data for all the countries.

for your information the same piece of code is working fine for other country in the production but here daily data volume is less when compare to the country which we are facing issue.

Regards,

Harish

Re: Start Routine Issue

anindya_bose — Sat, 07 Sep 2013 13:59:26 GMT

Hi Haraish

First thing you should do is to check which part in DTP is taking time ... Open DTP monitor and let us know which part is taking time...

1) start routine

2) Field routine ( Rules)

3) End Routine

4 ) Conversion of Char to SID....

or Extraction from the DSO is taking time?

Cheers

Anindya

Re: Start Routine Issue

anshu_lilhori — Sat, 07 Sep 2013 15:06:15 GMT

Hi,

Production system is pretty different than other system and lot of time it happens the things which run fine on DEV and QA may not work on PRD.

I would suggest to run the load when the access to the server is minimum may be in the late evening and also make sure no load is happening that time and see if that makes some difference.

As you are guessing that it might be the cause with indexing then in RSRV you may do complete check of those objects.

Another thing as pointed out by Bose--check which process is taking longer time.

Hope this gives you some clue to move ahead.

Regards,

Re: Start Routine Issue

former_member184884 — Sun, 08 Sep 2013 00:44:52 GMT

Hi Bose,

I've attached the screenshot for your reference do let me know if you need any inputs from my end.

Regards,

Harish

Re: Start Routine Issue

former_member184884 — Sun, 08 Sep 2013 01:09:46 GMT

Hi Kamal,

Thanks for your valuable inputs, i'll accept there is so wide scope to improve my code but my real concern is already expressed here.

t_material ,t_matunit ,t_primrate. are used in field routines and as well as in end routines to perform some business logic.

rest of the code is working fine because we have not touched that piece of code as it is not time consuming one so we have just concentrated on to optimize start routine code.

i have attached screenshot DTP run monitor screen hope it may help you in some extent for better understanding of my issue.

Please do let me know if you need any inputs from my end.

Kind Regards,

Harish

Re: Start Routine Issue

anshu_lilhori — Sun, 08 Sep 2013 06:45:41 GMT

Hi Harsih,

One thing is clear after seeing the above screen shot that issue is with the start routine only.

As this process is taking time of 4 hrs and 3 hrs so on you can also check the same.

I would suggest still to get your code optimized with some good Abaper and let them also know that this process is consuming time.

Also you can think of creating secondary indexes on the Dso fields which are not keyfileds and used in where statement.

Check out this document:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/a068bb0a-daf5-2e10-f6b8-fc3076070ced?QuickLink=index&overridelayout=true

Hope that helps.

Regards,

Re: Start Routine Issue

KamalMehta — Sun, 08 Sep 2013 10:30:17 GMT

Hi Harish ,

Its clearly evident from your screenshot that start routine is consuming most of the time.

As we have already given you several pointers where you need to check and take care of i.e. Select * and loops if possible in your code , Populate t_/BI0/PCOMP_CODE with company code only.

Please optimize them in the code and lets verify the DTP runtime now.

Thanks

Re: Start Routine Issue

Former Member — Sun, 08 Sep 2013 14:42:47 GMT

Hi Harish,

Please try to implement the suggestions given by our experts in your code. I would advice you to take the help of any ABAPer.

One important thing is please check the number of parallel processes for a your DSO to Cube DTP.

Because it seems same code took 15 minutes in Quality. but it's taking long time in Production.

So in your DSO to Cube DTP screen navigate to below path.

Goto -> Batch Manager Setting. Under Number of Processes, check how many background processes used to process the DTP for Quality and Production.

I doubt there are more number of process allocated in your quality than Production that's why it's taking less time in quality.

Harshawardhan.

Re: Start Routine Issue

anindya_bose — Sun, 08 Sep 2013 18:49:08 GMT

Ok Harish.. Good , now I can see where exactly DTP is spending time.

Overall this code was done in inefficient manner . Instead of definition your internal table as TYPE of P table of Material , you should only take those fields which you need and only select those instead of using select *.

loop at t_/BI0/PCOMP_CODE into wa_/BI0/PCOMP_CODE.

wa_r_compcode-sign = 'I'.

wa_r_compcode-option = 'EQ'.

wa_r_compcode-low = wa_/BI0/PCOMP_CODE-COMP_CODE.

wa_r_compcode-high = ''.

append wa_r_compcode to r_compcode.

endloop.

IF SOURCE_PACKAGE[] IS NOT INITIAL.

REFRESH: t_primrate.

DELETE SOURCE_PACKAGE[] WHERE comp_code NOT IN r_compcode.

basically you are taking only those comp code for which country is PK ... Deleting anything else from the Source_Package.. right?

Either you should do this in DTP filter , if country selection is available. Or, you should do this step before executing any other select statement.

So basically if you delete all other data from source_package where Comp Code is not in your range table, then "For All Entries" statement would select much lower data set for every select and would increase the performance.

Please let me know if my understanding is correct.

Cheers

Anindya

Re: Start Routine Issue

KamalMehta — Mon, 09 Sep 2013 01:53:24 GMT

Hi ,

You should also change DTP setting to have less data packet size and more number of parallel process.

Thanks

Re: Start Routine Issue

former_member184884 — Mon, 09 Sep 2013 05:57:50 GMT

Hi Bose,

DELETE SOURCE_PACKAGE[] WHERE comp_code NOT IN r_compcode.

this piece of code is not their earlier but which is added recently by one of my ABAPer colleague he told me it will improve performance so even i am aware of it does not make much sense because here source package is DSO means it is not common DSO, it is country specific DSO so obviously it will have country relevant data only.

Because he is core ABAPer i don't have option to listen his words.

as you are saying there is code needs to be change do let me know what part of code needs to change so that we can discuss in team and proceed accordingly.

Regards,

Harish

Re: Start Routine Issue

anindya_bose — Mon, 09 Sep 2013 07:28:36 GMT

Harish

Let me explain you in detail...

Part A:

select *

into table T_/BI0/PCOMP_CODE

from /BI0/PCOMP_CODE

where COUNTRY eq 'PK'

and OBJVERS eq 'A'.

This piece of code is selecting all company codes where Country is PK.

Part B:

loop at t_/BI0/PCOMP_CODE into wa_/BI0/PCOMP_CODE.

wa_r_compcode-sign = 'I'.

wa_r_compcode-option = 'EQ'.

wa_r_compcode-low = wa_/BI0/PCOMP_CODE-COMP_CODE.

wa_r_compcode-high = ''.

append wa_r_compcode to r_compcode.

endloop.

This piece of code is filling up a range table ; only with those comp codes which has country = PK.

Part C :

DELETE SOURCE_PACKAGE[] WHERE comp_code NOT IN r_compcode.

This part is deleting any source data where comp code is not in range table; that means when Country for Comp Code is not PK.

so basically you are left with only those records which are for comp codes which has country as PK.

I am not sure if this is your business requirement . But if you use this Part A, Part B and Part C before issuing any other SELECT statement , that will decrease the total number of records fetched from database table and routine will finish faster.

Another thing you can look at...

SELECT *

INTO TABLE t_primrate

FROM /bic/azsd_o01900

FOR ALL ENTRIES IN SOURCE_PACKAGE

WHERE salesorg EQ SOURCE_PACKAGE-salesorg

and comp_code EQ SOURCE_PACKAGE-comp_code

and division EQ SOURCE_PACKAGE-division

and distr_chan EQ SOURCE_PACKAGE-distr_chan

and calday GT min_calday

and customer EQ SOURCE_PACKAGE-/bic/zsd_dist

and material EQ SOURCE_PACKAGE-material.

ENDIF.

how many records do you have in DSO zsd_o019. What are the keys of this DSO? Just change the order of Where like below...and see if you get some better performance.

SELECT *

INTO TABLE t_primrate

FROM /bic/azsd_o01900

FOR ALL ENTRIES IN SOURCE_PACKAGE

WHERE comp_code EQ SOURCE_PACKAGE-comp_code

and calday GT min_calday

and salesorg EQ SOURCE_PACKAGE-salesorg

and division EQ SOURCE_PACKAGE-division

and distr_chan EQ SOURCE_PACKAGE-distr_chan

and customer EQ SOURCE_PACKAGE-/bic/zsd_dist

and material EQ SOURCE_PACKAGE-material.

ENDIF.

You can also create Secondary Index on Comp Code for this DSO

Cheers

Anindya