SQL Server StoredProc vs UDF inline table

Question

I have written a complex query that will return me a list of IDs. Now I want to re-use this query so that I join the results with another query. For that I plan to put this into a Stored Proc or an UDF and then use it to insert into a temp table.

Something like below

1) Put the query in a Stored Proc and insert it into temp table

INSERT INTO #TEMP
EXEC SP_COMPLEX(@PARAM1,@PARAM2...@@PARAMN)

2) Put the query in a UDF and insert it into temp table

INSERT INTO #TEMP
SELECT ID_LIST FROM DBO.UDF_COMPLEX(@PARAM1,@PARAM2...@@PARAMN)

I can't see significance difference between the two when I run them for a result of 1000 IDs. But in real implementation the result may be a million rows.

For performance which one would be better ?

Answer 1

Rather than guessing, I suggest you compare the execution plans for both techniques.

User defined functions can be convenient, they can also sometimes cause performance to suffer. This problem with them is that they use row-by-row processing, similar to a cursor, instead of a set-based operation. So if the result set of your query, then the performance impact will be small. But if the result set is large, then performance could very well become a problem. Generally speaking, if you are using a user defined function, you will want to avoid using them with large result sets. Use a stored procedure instead.

One way to improve performance of scalar valued User-Defined Functions is by converting them to table-valued ones.

Answer 2

I found using an inline table valued function and directly joining it is the better and efficient option than the options I had asked in the question. Something like

SELECT MYTABLE.MYFIELD FROM MYTABLE, DBO.UDF_COMPLEX(@PARAM1,@PARAM2...@@PARAMN) MYQUERY
WHERE MYTABLE.KEYVALUE = MYQUERY.KEYVALUE