I am facing a severe performance problem. I have a query which calls a function and select data from a primary table which has 30 million rows. The function uses the data in the same table for each row and returns a values corresponding to the inputs of the column of each row. If I select the query I can get all the result set in 30 secs. However, when I insert the data it causes a severe performance issue. Basically, the function call happens for each row and 200-300 rows are processed per second. I tried and store the output of the function return in memory so that I can bulk collect and insert those values, but it seems when i fetch the records the function call happen again.
Is there some way by which the select statements result of the function call be stored in memory and dump directly into the table.(ie 30 million) return values. I have attached the function and the bulk insert. I have tried using result cache but it does not seem to be working.
Thanks a lot for you help in advance