SQLscript procedure - Performance optimisation

[Former Member]

Hello Experts,

Continuing from the below discussion :

and thanks to valuable inputs form I was able to zero in on the problem area.

What I learnt in the process(screenshot from planviz attached ) is that two statements take up 99% of time.

I have pasted the two statements below for reference ( I tried my best to better format sql statements for readability as it is not always the best approach to copy past sql statements )

Also, these two statements form the heart of the functional  logic.

This is part of a Revenue allocation code, where revenues ( or costs ) is allocated from  a sender to a receiver -

based on  characteristics from sender and receiver. (join condition ).

Distribution base is used to create the multiplying factor,

and allocation is done using - S."AMT"  * (R."DIST_BASE" / D."DIST_BASE" ) - ( a keyfigure in Receiver is defined as receiver_distbase )

I'm not  a functional expert in this topic, and sharing what I understood from functional expert.

Not going too much into functional topic,

Wanted to understand what is wrong with the below sqls.

I could already see that case statement is a row operation and will slow down the prcess drastically.

also "IN" in join condition which is also a performance glitch.

I understood from functional expert that this is necessary .

these two statements take around 20 seconds each which i feel is a lot  considering data volume.

Any pointers on how I should proceed.??

Thanks in advance.

Best Regards,
Vinay

lt_sender2 - 65 rows.

lt_distbase  - 65 rows

lt_receiver - 136535 rows.

  lt_distbase =  SELECT ROW_NUMBER( ) OVER ( ) AS "FS_PER_D_ROW_", 1 AS "FS_PER_DUMMY_", S."YEAR_MM", S."DEPT_CD", S."DEPT_GRP", S."UPPER_DEPT_GRP",

  CASE

   WHEN  SUM( IFNULL( R."DIST_BASE", 0 ) ) = 0  THEN TO_DOUBLE(1)

   ELSE  SUM( R."DIST_BASE" )

  END

  AS "DIST_BASE"

  FROM

:lt_sender2 AS S

  LEFT OUTER JOIN :lt_receiver AS R

  ON ( S."FS_PER_DUMMY_" = R."FS_PER_DUMMY_" ) AND

( S."YEAR_MM" IN ( R."YEAR_MM", R."FS_PER_YEAR_MM" )) AND

( S."DEPT_CD" IN ( R."DEPT_CD", R."FS_PER_DEPT_CD" )) AND

  ( S."DEPT_GRP" IN ( R."DEPT_GRP", R."FS_PER_DEPT_GRP" )) AND

( S."UPPER_DEPT_GRP" IN ( R."UPPER_DEPT_GRP", R."FS_PER_UPPER_DEPT_GRP" ))

  GROUP BY S."YEAR_MM", S."DEPT_CD", S."DEPT_GRP", S."UPPER_DEPT_GRP";

  lt_allocation = SELECT  S."FS_PER_S_ROW_", S."FS_PER_DUMMY_", IFNULL(R."FS_PER_R_ROW_", 0) AS "FS_PER_R_ROW_",

IFNULL( D."FS_PER_D_ROW_", 0) AS "FS_PER_D_ROW_", IFNULL( R."YEAR_MM", S."YEAR_MM" ) AS "YEAR_MM",

IFNULL( R."PLCY_NO", TO_NVARCHAR('') ) AS "PLCY_NO",  IFNULL( R."CLERK_NO", TO_NVARCHAR('') ) AS "CLERK_NO",

   IFNULL( R."DIST_TYPE", TO_NVARCHAR('') ) AS "DIST_TYPE",  IFNULL( R."COST_CENTER", TO_NVARCHAR('') ) AS "COST_CENTER",

   IFNULL( R."DEPT_CD", S."DEPT_CD" ) AS "DEPT_CD",  IFNULL( R."OBJ_TYP", TO_NVARCHAR('') ) AS "OBJ_TYP", 

IFNULL( R."OBJ_SEQ", TO_NVARCHAR('') ) AS "OBJ_SEQ",  IFNULL( R."PROD_CD", TO_NVARCHAR('') ) AS "PROD_CD",

IFNULL( R."PROD_GRP", TO_NVARCHAR('') ) AS "PROD_GRP", IFNULL( R."MANDT", TO_NVARCHAR('') ) AS "MANDT",

IFNULL( R."DEPT_GRP", S."DEPT_GRP" ) AS "DEPT_GRP",  IFNULL( R."UPPER_DEPT_GRP", S."UPPER_DEPT_GRP" ) AS "UPPER_DEPT_GRP",   IFNULL( ( S."AMT" * 100 / 100 * R."DIST_BASE" / D."DIST_BASE" ) , S."AMT" ) AS "AMT"

  FROM

:lt_sender2 AS S

  LEFT OUTER JOIN :lt_receiver AS R

  ON ( S."FS_PER_DUMMY_" = R."FS_PER_DUMMY_" ) AND

( S."YEAR_MM" IN ( R."YEAR_MM", R."FS_PER_YEAR_MM" )) AND

  ( S."DEPT_CD" IN ( R."DEPT_CD", R."FS_PER_DEPT_CD" )) AND

( S."DEPT_GRP" IN ( R."DEPT_GRP", R."FS_PER_DEPT_GRP" )) AND

  ( S."UPPER_DEPT_GRP" IN ( R."UPPER_DEPT_GRP", R."FS_PER_UPPER_DEPT_GRP" ))

  LEFT OUTER JOIN :lt_distbase AS D

  ON ( S."FS_PER_DUMMY_" = D."FS_PER_DUMMY_" )

AND ( S."YEAR_MM" = D."YEAR_MM" )

AND ( S."DEPT_CD" = D."DEPT_CD" ) AND

  ( S."DEPT_GRP" = D."DEPT_GRP" ) AND

( S."UPPER_DEPT_GRP" = D."UPPER_DEPT_GRP" );

[lbreddemann]

As we don't have your requirement, nor the base tables or test data, there's little option to propose a better way to implement your logic.

However, your SQL snippets shows a few "smelly" coding techniques:

  lt_distbase =  SELECT ROW_NUMBER( ) OVER ( ) AS "FS_PER_D_ROW_"
  , 1 AS "FS_PER_DUMMY_"
  , S."YEAR_MM"
  , S."DEPT_CD"
  , S."DEPT_GRP"
  , S."UPPER_DEPT_GRP"
  ,  CASE
  WHEN  SUM( IFNULL( R."DIST_BASE", 0 ) ) = 0 
  THEN TO_DOUBLE(1)
  ELSE  SUM( R."DIST_BASE" )
   END  AS "DIST_BASE"
  FROM
  :lt_sender2 AS S
  LEFT OUTER JOIN :lt_receiver AS R
   ON  ( S."FS_PER_DUMMY_" = R."FS_PER_DUMMY_" )
   AND ( S."YEAR_MM" IN ( R."YEAR_MM", R."FS_PER_YEAR_MM" ))
   AND ( S."DEPT_CD" IN ( R."DEPT_CD", R."FS_PER_DEPT_CD" ))
   AND ( S."DEPT_GRP" IN ( R."DEPT_GRP", R."FS_PER_DEPT_GRP" ))
   AND ( S."UPPER_DEPT_GRP" IN ( R."UPPER_DEPT_GRP", R."FS_PER_UPPER_DEPT_GRP" ))

  GROUP BY S."YEAR_MM", S."DEPT_CD", S."DEPT_GRP", S."UPPER_DEPT_GRP";

Generating a row number? What for? This typically indicates a slow row-by-row processing approach.

Generating the constant 1 value? Again, what for?

Then there is the ongoing use of IFNULL() in your statements. You really should make sure to tackle NULLs once (and only once) as otherwise a lot of record by record processing needs to be applied.

Using a LEFT OUTER join might be syntactically correct, but as you seem to rather use a lookup/matchup, you might consider using a sub-select instead ( that's just a semantic thing).

The join condition above appears to be "over-engineered" - what's the reason behind the double field joins here? Is the data model ambiguous?

[Former Member]

Hello Lars, Thanks for your response,

Appreciate you spending time on this

The issue here is , this procedure is written by an abaper for a very specific(read complex) functional requirement of Cost and revenue allocation.I'm tasked with tuning this for performance . so I might not have complete answers with respect to functional requirements but will try my best

answers your questions :

Generating a row number? What for? This typically indicates a slow row-by-row processing approach.

what I learnt from the functional person is that while allocating a sender and receiver there are no primary keys on both sides and this will help identify un-assigned values ( identifying those sender records with no matchin receiver records - which will become eligible for some special processing for allocation ).

Generating the constant 1 value? Again, what for?

This is something that i think can be done away with.

Then there is the ongoing use of IFNULL() in your statements. You really should make sure to tackle NULLs once (and only once) as otherwise a lot of record by record processing needs to be applied.

yes, There is a lot of scope for removing many ifnulls.

Using a LEFT OUTER join might be syntactically correct, but as you seem to rather use a lookup/matchup, you might consider using a sub-select instead ( that's just a semantic thing).

The join condition above appears to be "over-engineered" - what's the reason behind the double field joins here? Is the data model ambiguous?

This is where I feel there is something that needs to be done.

The join condition e.g  ( S."YEAR_MM" IN ( R."YEAR_MM", R."FS_PER_YEAR_MM" ))

the S.YEAR_MM is the sender year &

R.YEAR_MM is receiver year value and

R."FS_PER_YEAR_MM"  is just '' ( empty/no value ).

the idea behind this is if a sender characteristic is coming in as ''(empty/no value),

it should be allocated to all the receiver rows and hence the in condition.

this basically has to be done with all the sender characteristics and hence all the join conditions have IN condition.

Regarding the point of using sub-query,Im not sure if that will be possible as currently we need to check against all characteristic from sender for match against characteristics in receiver( match values and '' empty values).

And from what I've seen in some examples, even in sub-queries, the explain plan is actually doing a join ( but this is what ive seen in one or two examples and im not sure when the optimiser decides to join and decides to go the sub-query way)

Regarding base tables and data, I'm not sure how i can provide this, as the whole procedure is pretty long with many intermediary temp tables.what we see here as two sql snippets is the problematic statements.

but what we deal with is pretty much a set of characteristics and key-figures on both sender and receiver side.

Thanks again,

Vinay