Assignment to an OUT variable in procedure is taking a lot of time

[former_member373049]

Hi,

I have a table with 22,000 rows . i have written a procedure to do certain selects on that table and return a result set. previously i was doing a      "select * from table" to obtain result and it was returning within milli seconds.But later i added an out variable and assigned the result obtained from select i.e "out_variable = select * from table".

Now the procedure started taking 6-7 minutes to execute the procedure.I'm not sure as to why this is happening. there was no change in the logic but only assigning it to an out_variable.I tried creating a table type and using it for out_variable which was not fruitful. i also tried creating the type in the procedure head as "create procedure abc ( in a, in b, out variable table(....)) " .  simply put assigning the result to the variable is taking too long.

Could you help me as to why this is happening ? do i need to change any configs?

Thanks,

Srinivas

[lucas_oliveira · Accepted solution]

Hi Srinivas,

You're probably materializing the whole table when doing so and generating an intermediate result (variable out) with a copy of it to be kept as the resultset.

Depending on how wide the table is and presence of LOBs for instance can affect as well.

Share the executed planviz and the actual code for better understanding.

BRs,

Lucas de Oliveira

[former_member373049 · Accepted solution]

Hi Lucas,

     Thank you for helping me out.

Below is my code.

drop procedure TEST_SCHEMA.TEST_PROCEDURE;

create procedure TEST_SCHEMA.TEST_PROCEDURE  (OUT DOCUMENT_REDUCED table
(id integer, hash varchar(100), source varchar(500), headline varchar(1000)))
LANGUAGE SQLSCRIPT 
AS
BEGIN

DOCUMENT_REDUCED = select id,hash,source,headline from  TEST_SCHEMA."DOCUMENTS" where id <= 5 ;

END;

call TEST_SCHEMA.TEST_PROCEDURE(?);

the above call takes around 7 minutes to be executed even though my table has only 18 rows now and needs to fetch 10 rows.

Statement 'call TEST_SCHEMA.TEST_PROCEDURE(?)' 
successfully executed in 7:06.277 minutes  (server processing time: 7:06.086 minutes)
Duration of 3 statements: 7:06.575 minutes 
Fetched 10 row(s) in 0 ms 147 µs (server processing time: 0 ms 0 µs)

if out_variable is not in procedure argument, then the select statement returns the result set within few milliseconds.

a screen shot of plan viz has been attached.

Thanks,

Srinivas

[former_member373049]

Hi,

     I started working on HANA  SP12. previously i had  SP11 . Finally i was able to make assignments and procedures ran fast without any hassle. I'm not sure what the problem was,but this wasn't the case in SP12.

     Thank you everyone.

Regards,

Srinivas

[anindya_bose]

I also faced this problem 2 days back while Plan Viz was perfectly okay but a simple query was taking 2 mins to run instead of few mili seconds.  Due to some maintenance activity, basis had to restart HANA server. After that  I did not see this problem again .  May be allocation of threads /CPU was not happening properly for some reasons which was not known to us.

[SergioG_TX]

adding to Lucas

change the definition of the table output to be a table type then use the table type as the data type of your output variable.. i have seen that being faster than declaring an inline output table .. i dont know why but i have seen those results.

[former_member373049]

Hi Sergio,

     Before defining the output table inline , i did try creating a table type and using it as data type. but i couldn't see the difference. it was still taking few minutes for execution.until i remove the assignment to a variable inside the procedure, it takes minutes to execute.

[lucas_oliveira]

Hi Srinivas,

A screenshot of you planviz does not help that much. Provide the plv file.

ps.: as an SAP employee you can ask that question on internal SAP channels

[former_member373049]

Hi Lucas,

I've attached a plv file below.  since plv file is not supported here, I've made it a .text file.

please convert it back to plv file.

test.txt.zip

[lucas_oliveira]

Hi,

Please record a HANA performance trace with function profiler and plan generation on and open an SAP incident. We need to look a little closer here.

Thanks,

Lucas de Oliveira