Hana compile time of a stored procedures

[Former Member]

Hello,

When looking at our vizplan of various stored procedure call, the compile time of every procedure seems to be a significant cost of execution.  I was expecting to have compile time on the first call of a stored procedure, then subsequent calls to that plan to have minimal compile time. 

Is it fair when seeing significant compile times (500ms - 1000ms) on a call to a procedure consistently that something inside the procedure is forcing a recompile?  Is there anyway to avoid recompile a procedure and used a previously cached plan (not sure if valid Hana lingo).? 

[lbreddemann]

Where are you looking up the numbers?

What is your SAP HANA revision?

What are the exact commands you use?

Generally there is no way to change a procedure and have it use an execution plan that was compiled for a previous version. That works in no DBMS that I know of. As the procedure got changed a new compilation is required.

One major contributor to recompilation though could be the change of underlying/depending objects.

If there's a lot of changes happening there, this might explain what you're seeing.

Cheers,

Lars

[Former Member]

Thank you Mr. Breddermann,

Checking these numbers in two places:

1. PlanViz-->Overview-->Time:Compilation
2. PlanViz-->Statement Statistics-->Total Compile Time (ms)

We are revision 96

We call these procedures via JDBC with CALL Proc(Params)

Our base tables for most calls sourced via SLT

My newbie thinking was after the first compile of a procedure into the environment, the first call from a client would persist all the statements of the proc. into a procedure cache.  This would cause a long compile time and first run.  All subsequent client calls to the same proc. with the same input types etc. would not need to recompile all the statements but fetch the precompile statements from the cache and execute.

Thank you for the time.

[lbreddemann]

Well, without seeing your actual code there is not much we can add to this.