Skip to Content
Jun 15, 2016 at 08:08 AM

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



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?