on 05-07-2014 10:55 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Hi Krishna
LAG itself is taking 10,533.698 ms .!!
Is there any workaround which can give me same output as LAG ?
Regards
Kumar
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
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
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
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.
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
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
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.