Behavior of Table Function or Stored Procedure in a calculation view

[Former Member]

Hi,

I have a complex piece of SQL Script embedded in a .htbtablefunction.  The function takes 30 seconds to run.  The Result Table is summarized data that produces a very small number of rows.

I use that table function in a calculation view as follows:

BEGIN

var_out =

select * from  "MYSCHEMA"."MYPACKAGE::MYFUNC" ();

END

With every drag and drop in Data Preview (or Analysis for Excel), it is taking 30 seconds per move.  So with each move in Data Preview it seems like it calls the whole function again, reading the large fact table, applying logic, then finally returning the summarized result set.

My question is:  Is there a way to store the results in a temporary table (or other artifact) and make the temporary table available to the scripted calculation view?  I'm trying to achieve a one time processing of the data (~30 seconds), storing the small result set into an object that will be very fast in a calculation view.

Is this possible?

[gopinath_kolli · Accepted solution]

Please create a table in the data base. in the procedure load the data from table function to that table and use that table in calculation views.

In this case you can meet your requirement.

I hope this is one of the solution.

Generally when we are providing data to reports we use to do this so that we will run the procedure once and use the table data to get the report results faster.

hope this helps.

[Former Member · Accepted solution]

Yeah, I thought that was the only way.  We were trying to avoid a physical column table, but it makes sense why we need to.  Thanks.