cancel
Showing results for 
Search instead for 
Did you mean: 

Best method to use LAG - A window function in Scripted calculation View.

former_member184871
Contributor
0 Kudos

Hello Community,

I am struggling with performance issue - with LAG - a window function when used in Scripted calculation view. In my scenario I am having a single column table with 7 columns and 1.5 Million records.

When I run the Calculation view (scripted) then output takes more than 10 seconds.

Please suggest the best possible way to use the LAG function on a single table.

Awaiting response.

Regards

Kumar

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member182114
Active Contributor
0 Kudos

Hi Kumar,

As you said the source have 1.5mi rows, but the question is. Are you making the window function over 1.5mi?

Is your filters properly set to provide a filter push down?

Do you have input parameters to reduce the dataset before windowing?

You can answer all these questions investigating the Visual Plan.

Regards, Fernando Da Rós

former_member184871
Contributor
0 Kudos

Hi Fernando,

I am using the window function on 1.5mi. So in this case is it normal ? or is there any other workaround to use Lag function.

Considering the present requirement I am not suppose to use filters and Input parameter.

Any thoughts ?

Regards

Kumar  

former_member182302
Active Contributor
0 Kudos

Hi Kumar,

Can you check the Viz plan? is the LAG function consuming that "Extra" time?

Regards,

Krishna Tangudu

former_member184871
Contributor
0 Kudos

Hi Krishna

LAG itself is taking 10,533.698 ms .!!

Is there any workaround which can give me same output as LAG ?

Regards

Kumar

former_member182302
Active Contributor
0 Kudos

Hi Kumar,

Window functions work on ROW engine as far as i knew.

Have a look on this blog, which tells on the work around of rank, may be a similar thought process can help you to get this function using graphical model.

If you are able to do so then you would be using the "Column" engine .

Regards,

Krishna Tangudu

former_member184871
Contributor
0 Kudos

Hi Krishna,

Thanks for guiding, will give it a try and get back to you.

Regards

Kumar

former_member184871
Contributor
0 Kudos

Hi Krishna,

I have the corresponding SQL for LAG but converting them in Graphical calc view is not giving the desired output.

Can you elaborate LAG with any example with Graphical Calc view.


Regards

Kumar  

former_member182302
Active Contributor
0 Kudos

Hi Kumar,

Can you share the SQL here.. I will check to see if i can convert into the Graphical model.

Regards,

Krishna Tangudu

former_member184871
Contributor
0 Kudos

Hi Krishna,

Here is the SQL that I am using as of now:


BEGIN

var_out =

  SELECT

    "SNO"   AS "SerialNum",

    "TRADE ID"     AS "TradeID",

  "PROFIT CENTER" AS "ProfitCntr",

  "PORTFOLIO"   AS "Portfolio",

  "TRADER"       AS "Trader",

  "DATE"     AS "Date",

  "TRADE AMOUNT" As "TradeAmount",

LAG  ("TRADE AMOUNT", 1,0) 

OVER ( PARTITION BY "PORTFOLIO"  ORDER BY "TRADE ID" )  AS "OldAmount",

  ("TRADE AMOUNT" - LAG ( "TRADE AMOUNT", 1,0)

OVER ( PARTITION BY "PORTFOLIO"  ORDER BY "TRADE ID" )) AS "DiffAmount"

FROM

"WORKSHOP"."CP_TRADE_TRNSCT_NEW";

END

Regards

Kumar

Former Member
0 Kudos

I've avoided window functions for most of my HANA experience and plan on keeping it that way.

If you can describe what you're trying to accomplish from a functional perspective (yes, I'm too lazy to work it out from what you've written ), I'll be happy to try and propose an alternative approach if possible.

former_member184871
Contributor
0 Kudos

Hi Jody,

Its great to know that you have avoided the window functions, probably you can help me 

My scenario - is of commodity trading. In the above sql > LAG gives the previous record amount to the next record. So for example on June 1 business opens at 100$ (Trade Amount)  and closes at 200$ so for june 2 the business will open @ 200$ and so on.

So basically on Trade amount I am applying LAG (gives me old amount) and then  doing a difference(gives difference amount) which is like closing  of business and opening of business

Please advice.

Regards

Kumar

Former Member
0 Kudos

Hi Kumar,

Here's my recommendation. As always there's likely quite a few solutions. But here's where I'd start.

First, a question - you describe dates i.e. June 1, June 2 - but in your SQL I see TRADE_ID. I'm going to assume TRADE_ID is date-related, some kind of chronological indicator, of type INTEGER.

1) Create a new "generated column" (persisted calculated field) as follows:

ALTER TABLE "WORKSHOP"."CP_TRADE_TRNSCT_NEW" ADD ("TRADE_ID_NEXT" INTEGER GENERATED ALWAYS AS "TRADE_ID" + 1);


2) Self join the table to itself, on all required key fields, plus T1.TRADE_ID = T2.TRADE_ID_NEXT


3) Do the calculation subtracting amounts.


All of the above can be done graphically very easily - except the generated column of course, you have to execute that command (and make sure it's somehow maintained in case table gets dropped for some reason).


Details on generated columns (scroll down a bit, also do a google search for more related topics) - ALTER TABLE - SAP HANA SQL and System Views Reference - SAP Library

Also, as noted above - window functions execute in row store, hence the slower performance.

Cheers,

Jody

former_member184871
Contributor
0 Kudos

Hi Jody,

Thanks for the suggestion, I am working on it, will update accordingly.

Regards

Kumar