1

I work with an application that, in many places, uses stored procedures to return data to the application, kind of like a view, but these procedures take in parameters (which isn't possible in a SQL Server view).

I believe this was done due to it being more efficient for the given queries to have values passed deep into the inner queries, than it was to use a view with a WHERE clause.

In these cases, none of the other features you get by using a stored procedure are required e.g. they are all only select statements... no transactions, it's a single result set, no error handling, no calling to other procedures, etc.

For this specific use case - which is basically a "parameterized view" - are there efficiencies to be gained by using an inline user-defined function that returns a table data type instead of stored procedures for this?

The use case I am dealing with involves very small result sets. A simple example would be where a web app calls a proc with, say, a member id parameter, the proc contains a query with a bunch of joins and subqueries etc. (the parameter is used deep within these) and then send back a list of, say, subscriptions, owned by that user.

Is one method generally better than the other?

  • 3
    These terms are very hand-wavy to me. The impact of using a stored procedure for something, or not, is entirely about what code the stored procedure uses, not the basic fact of using a stored procedure. Similarly, when you have a where clause against a view, or the underlying table, does not change how deep into the queries those clauses go. Could you add some specific code examples of the view and stored procedure, as well as the underlying table, its indexes, and exactly how the values are passed to the stored procedure and/or are constructed into a query in the application? – Aaron Bertrand Apr 10 '18 at 16:16
  • 1
    PLEASE READ. – Aaron Bertrand Apr 10 '18 at 19:22
  • @AaronBertrand - I get where you are going with your comments, but that link applies to asking a question about "a problem caused by your code" - In my case, the code doesn't have a problem, per sé. I am asking this question at a broader level (and perhaps this site isn't appropriate for this type of question) I was more asking if one method was generally better than/or generically optimal over the other. I guess that one could consider the question too broad, and I would understand if you were to propose it be closed based on that. – GWR Apr 11 '18 at 19:06
2

The answer partially depends on what you do with the output of the object. Both stored procedures and table-valued functions support inserting into table, but using an inline table-valued function can be significantly more efficient.

Below are a few silly object definitions for the demo. Both of them generate a result set of about 6.4 million rows with a single column.

CREATE PROCEDURE dbo.DUMMY_PROCEDURE (@i BIGINT)
AS
BEGIN
    SELECT CAST(NULL AS BIGINT) COL
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    WHERE @i <> 0;
END;

GO

CREATE FUNCTION dbo.DUMMY_FUNCTION (@i BIGINT)
returns table
as
return (
    SELECT CAST(NULL AS BIGINT) COL
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    WHERE @i <> 0
);

GO

Next I'll insert the results of the stored procedure into a temp table. In general, this isn't a good thing to do for result sets that aren't tiny. The results are written to a separate internal structure before being inserted into the table. SQL Server uses a cardinality estimate of 1 row for the insert which can also cause performance issues.

DROP TABLE IF EXISTS #TARGET_TABLE;
CREATE TABLE #TARGET_TABLE (ID BIGINT);

-- CPU time = 11514 ms,  elapsed time = 10748 ms
INSERT INTO #TARGET_TABLE WITH (TABLOCK)
EXEC dbo.DUMMY_PROCEDURE 1;

The query took about 11 seconds. I uploaded the actual plan here if you want to take a look. Here's a screenshot of the worst part:

enter image description here

Things are much better with the function. The function definition is inlined and I'm eligible for parallel insert in SQL Server 2016. The insert takes about half a second.

DROP TABLE IF EXISTS #TARGET_TABLE;
CREATE TABLE #TARGET_TABLE (ID BIGINT);

-- CPU time = 1562 ms,  elapsed time = 520 ms.
INSERT INTO #TARGET_TABLE WITH (TABLOCK)
SELECT COL
FROM dbo.DUMMY_FUNCTION (1);

The query plan is healthier and much more typical:

enter image description here

There might be other internals differences in addition to that. I couldn't tell you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.