I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.
Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:
DataUserID, StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...
Or using a table-valued function to return a temp table as the result.
I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.
Table-Valued Function Result vs. Calculation Table
Raphinator
The fact of the matter is the data returned was a multi-dataset result from a stored procedure. Since MS Reporting Services does not support this return type I had to update the sp's to return only a single dataset result. Many of the sp are over 100 lines in length so the only way to return the data was how I described above. A little more info:
Currenly, a max 6 string fields 5 floats 2 dates and 7 int fields will be returned (worst case senario of 10 reports)
strval1,strval2,Intval1 - One report uses only 2 strings and 1 ints.
The sp will run very often.
The number of records created per sp call will never be more than 100.
Since this process is going to be run often it is important that I use the best performance solution.
[Edit] - I forgot each user will have different values. So worst case senario would be approx.100 users at a time times 50 records times (6 Char(75),5 Float, 2 DateTime, 7 INT) fields. Each users data will be flushed from the table at the end of the sp after the result has been returned. This is how I have implemented it.
I am debating changing the sp's to function table result and testing. Just wanted some advice before I try this because it could be a BIG waste of time.
MSDev23
I always say shy away from storing/holding unnecessary data if possible. Now, if these 10 reports can use the SAME data and only calculate it once, then by al means, create a table and hold it permanently until you need to refresh.
If the data will be used only once, then I would certainly try to use the table valued function, unless you are going to return thousands/hundreds of thousands of rows. Then it might be cheaper to put the data into a table so you can index it (then the optimizer can make good choices about what kinds of data it needs to solve a query)
I would even go so far as to say that you might not need the function, and just return it as a query, but I don't know your data. The more you can stuff into a single query (without any kind of function or view), the more likely it is to get a good plan from the optimizer. (Note that this is a rule of thumb, not always of reality. Sometimes this does turn out to not be the case.)
So a bit more information might be in order for a good answer, but as a rule of thumb, temp tables are bad, queries are good.
(note: I hope your real columns aren't named StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1. That would seem to be really hard to follow at times :)