Hana Procedure runs very fast and sometimes very slow

[former_member337435]

HI All,

There is a Hana procedure that calls a calculation view which contains a UNION of atleast 16 calculation views. This union is a union with constant mapping and based on the field( constant mapping) each sub VDM is invoked.

This is quite a complex VDM and this procedure tries to insert into a temporary table by selecting from the calculation view based on the WHERE condition of the field with constant mapping.

This procedure sometimes runs in 20 minutes for few days and then suddenly starts running for 8 hours at a stretch with just no change.

There are 3 nodes and I thought may be because most of the tables are in Node 1 and the procedure in the threads show it is running in Node 2, may be that's the problem.

But during the time it runs fast, on any node it runs fast. I do see that there are no blocked transactions as well. We tried flushing out the execution plans as well but that also did not help.

Has anyone faced a similar kind of issue and if so what was the resolution?

[eralper_yilmaz]

Hello Lalitha,

In general when a stored procedure is first executed, its execution plan is generated and cached for later use.

In case, if your procedure parameters effect the execution plan too much, the first execution parameters might cause an uneffective execution plan to be cached for following executions.

I mean, in general if the input parameters change the handling of SQL procedure by the SQL engine, the cached method of executing the procedure does not help too much for a different set of parameters.

It is better to identify such cases and call different procedures (although the same query works according to the selectiveness of values in WHERE clauses, etc, SQL Engine can cache different execution plans for each)

Or, EXEC or Execute Immediate could be called to run dynamic query which leads of dynamically creation of execution plan each time the procedure is called. Of course this is an overhead that must be paid

[former_member337435]

Thanks Eralper for your response. The input parameters are pretty much the same. So when we drop the execution plans, then I think it should run faster. But it didn't.

The WHERE clause also has the same values as well.

We did a re-org of one table and it did run faster. Not sure if that solved the issue.

Can you help me understand this statement better-

"It is better to identify such cases and call different procedures (although the same query works according to the selectiveness of values in WHERE clauses, etc, SQL Engine can cache different execution plans for each)".

I would not want to run a dynamic query either.

[eralper_yilmaz]

I wanted to say, it SP is for SELECT only, to read only a few rows filtering via a column which is very selective is different from reading a huge number of rows using a filter like Male/Female distinction, is very different from each other.

Maybe another reason for SP to perform slow might be the delta records, you know for column tables data is stored in a different manner than rowstore data. But these delta rows, which I want to say is recently inserted and updated rows, these are stored as rowstore data until they are merged with columnstore part of the table.

So, maybe reorganizing might merge all these parts of the table before executing the query on that table. Maybe you need a more frequent merge of this table updates. (DELTA MERGE is the terminalogy for this)