Convert Graphical view to Script view ?

[former_member290786]

Hello,

I would like to know, after searching some time on-line and couldn't find any answer for it - I there any way to converting a Graphical Calculation View, into Script view?

If there is, I would be thankful if someone could reference me to a guide.

Thank you,

Or.

[Former Member]

No, there is no way. They are generated with totally different processes now. I recommend you avoid the scripted views unless you need them.

[Former Member]

Hi John,

we noticed that on HANA 1.0.74 SQL Views works much faster then Graphical.

Denis.

[patrickbachmann]

Hi Dzianis,

This is a very interesting statement from you.  I was just wondering if you can elaborate at all on your specific example(s)?

Thanks,

-Patrick

[former_member182114]

Hi Dzianis,

There's no convertion to or from Script view. As you started with a Graphical view better you understand the points which is "failing" with planViz and do the needful.

On rev74 a graph calculation view can be automatic/internally converted to SQL when you select the option Execute In = SQL Engine (this is SP8 naming, a.k.a enforce SQL engine on your rev).

This have potential to boost your Graphical view without any other changing.

Another thing to pay attention is that SP7 really converted internally to a SQL and then submitted the SQL through SQL Engine. This changed on SP8 as they do not create SQL but directly the access path for the SQL Engine gaining even more speed.

If I were you I should flag this and make some tests.

Regards, Fernando Da Rós

[Former Member]

Hi,

Yes - statement is not really correct, sorry.

We was playing with performance converting manual Graphical view's into SQL's and got much better speed in SQL.

But real reason was the following: Graphical view in our case contains several nested view's (with different expressions/filters applied). Like:

• MainView has projection1 with NestedView1 inside and Expression1,
• MainView has projection2 with NestedView1 inside and Expression2,
• MainView has projection3 with NestedView1 inside and Expression3,

If this NestedView1 is heavy (takes 300-500ms) it is much better to have only one NestedView1 node and 3 projections on top of it like:

• MainView has projection0 with NestedView1 inside and Expression1 or Expression2 or Expression3,
• MainView has projection1,projection2,projection3 on top of projection0 with corresponding expressions.

While converting into SQL we skipped duplicated calls and this was the reason of my statement. Redesigning graphical view like described above gave us same speed.

Denis.

[former_member182114]

Hi Dzianis,

Understood your strategy and agre with what you are doing.

1)

You can try the old move to SQL from SP7 with below hint. BTW: This is not the current SP8 behavior and probably will be disabled soon:

select * from _sys_bic."pack/view"

(placeholder."$$CE_INTERNAL_RELATIONAL_OPTIMIZATION$$" => '1')

1 is the instruction to convert to SQL (it can be a big big monster) like it was default on SP7 when flagged enforceSQL=true.

As this is just for internal "move to SQL engine" you will not see the SQL unless you provoke a runtime error and borrow the SQL from indextrace.

This case of one "source" serving these projections can provoke:

- loss of filter push downs

- materialization of MainView (if choose CE)

- high CPU (if choose SQL)

2)

The great news is that HANA DEV continue to improve performance on each version. I've notice another gain on rev82 and there are rumors that things will be even better on SP9 (time for enhancements).

What I particular like to do is investigate, isolate the undesired behavior and reproduce it in a simplified way and create a ticket to SAP so they have a chance to analyze and improve performance.

You can't count with such enhancements for your current project, but they will be analyzed and improved in some time. Just a sample some tickets I created around rev73 will be realeased for SP9.

3)

Try this out on most recent version you can. Maybe what you have as problem was already solved.

There are some things like that that is not a problem anymore (double checked on planViz)

Best regards, Fernando Da Rós

[Former Member]

Hi Fernardo,

we tried on 83 and got 10-50% improvement without changing the code

[former_member182114]

Perfect that's the goal of HANA DEV.

I can't wait to check the performance we will achieve with SP9.

[Former Member]

Yes Or,

In the left pane of your screen, where you see the Graphical Calculation view you had created, You need to right click on the Graphical Calculation View and select Export to SQL. there you can find the entire back-end coding in the SQL script.

Kindly revert in case you need more inputs on this.

Thanks,
Collins

Message was edited by: Collins Sampson

[former_member290786]

Hi Collins.

First of all, Thank you for your reply.

Second, I can't find any export function.

Using REV74, I'm attaching a screenshot of a right-click on the view.

Can you refer me to the right place?

Thank you

[former_member199876]

No. there is no direct way to export a graphical view to a Script view.

You would have to manually re-construct the logic using CE functions.

CE_PROJECTION - for a projection node,

CE_UNION_ALL - for a UNION 

etc etc

Regards

Ajay