cancel
Showing results forΒ 
Search instead forΒ 
Did you mean:Β 

Difference of performance between table functions and Graphical views

0 Kudos

Hello all.

I'm having a curious performance problem in Hana.

I have a TF with some complicated logic and then a graphical view with just one node pointing to the TF.

So if I make a count(*) to the TF it takes 1 sec, and the same query but over the grafical view takes 30 secs.

Anyone has any explanation for this?

I need the help of some experts like lbreddemann πŸ™‚

Thanks

lbreddemann
Active Contributor
0 Kudos

Hey Juan,

please add a few more details to your question.

  • what HANA version are you using?
  • EXPLAIN PLAN output for both versions of the query.
  • If possible, screenshots of the PlanVIz.
  • If possible, show the code of the table function

Also, is the result of both SELECT COUNT(*) queries the same?

Accepted Solutions (0)

Answers (3)

Answers (3)

lbreddemann
Active Contributor

The attached EXPLAIN PLAN files contain the very same plan; I cannot make out a difference between them.

However, searching for the hint you found in Google led to this blog post How to investigate if Table User-Defined Function is unfolded or not? which points to a possible reason for the difference in runtime performance.

It appears that the graphical model does not get "unfolded" and thus misses out on SQL optimisations that lead to the better == faster plan.

Check the blog post and if that does not give you ideas on how to fix the query performance, I would recommend opening a support case with SAP.

0 Kudos

Lars, just one more comments.

We noticed in the plan, that actually what is executed is the TF but adding the hint

WITH HINT(DEV_NO_PREPARE_SQLSCRIPT_SCENARIO)

We don't know why this hint is added, and we don't find much info about it in google.

But if we manually execute the select directly from the TF with this hint, it goes to 40 secs, and without it 2 secs....

0 Kudos

Dear Lars,

The version is 2.00.059.01.1642499117 (fa/hana2sp05)

Yes, both selects returns the same number of records. Like I said, the graphical view is just one node with the TF.

Sorry I'm not sure if I can publish that information... Also is quite big.

Any way of sending all that info as a private message?

I hope you don't mind.

And many many thanks for the help.

lbreddemann
Active Contributor
0 Kudos

Sorry, a private message won't work.

This is a public forum; the whole point is to discuss topics in public.

For the visual information (planviz and graphical calc view) you may add screenshots.

For the EXPLAIN plan, usually one can re-format the textual table output to fit the forum page.

I have not been a regular in this forum for a couple of years now, as my professional focus has shifted.

Chances are that you can get a quicker/better answer by including the required information in the publicly visible question.

0 Kudos

Yes Lars, you are totally right.

This is the info:

The Calc View and its plan:

calcview.jpg

calcviewplv.txt

0 Kudos

The Main TF and the 3 TFs called (they are related to inventory):

main-functionsql.txt

tf-ru-fact-hist-stock-pricesql.txt

0 Kudos

Finally the explain plan for the TF:

tfplv.txt

tf2plv.txt