Stored procedure vs. Function performance SQL server

Some developers have a little confusion about when to create SQL function and when to create stored procedures? Some of the developers always create the stored procedures. Here we are going to discuss about performance impact between stored procedure and SQL function. 




To understand this better way I would like to first explain the difference between them. In simple word we can say stored procedure is a pre-complied database object which keeps batching of SQL statements and can return more than one result set. So inside a stored procedure we can write all types of DML, DDL, SELECT statements, Dynamic queries, implement error handling, invoking other stored procedure and SQL function and more thing. In the other hand SQL functions (SQL Server supports two types of the SQL function. They are SQL a scalar value function and tabled value function) are also pre-compiled objects which always return a single value or object. In case of scalar valued function, inside SQL function we cannot write DML, DDL, SELECT statements (allowed in a very limited way), dynamic queries, cannot implement error handling, cannot invoke other stored procedures etc. And in case of tabled value function very limited DDL and DML are allowed.

You may be thinking SQL functions have a lot of restrictions on SQL statement then why I am going to write SQL function!! Only benefit over a SQL function over a stored procedure is SQL function can be part of the SELECT statement and DML statements and which can improve overall query performance drastically.  Let’s discuss in detail when stored procedure will perform better and when a SQL function by examples.

a. Create scalar SQL function when there is need to get a single value after some calculation and that calculation is need to be done multiple times with different parameters. For examples: There is need to count the total comments sent by a user. 

Using stored procedure:

CREATE PROC uspUserCommentCount(

  @ntUserID AS BIGINT

)

AS

BEGIN

SELECT COUNT(*) FROM tblComment WHERE ntUserID = @ntUserID

  END

Using SQL function:

CREATE FUNCTION udfUserCommentCount(

     @ntUserID AS BIGINT

)

RETURNS INT

AS

BEGIN

RETURN (SELECT COUNT(*) FROM tblComment WHERE ntUserID = @ntUserID)

END

If we execute only times then we will not get a significant performance difference:

EXECUTE dbo.uspUserCommentCount 10

Total logical read: 4

SELECT dbo.udfUserCommentCount(10)

Total logical read: 4

If there is business need to get total comments of all users according to users by using either SQL Function or Stored procedure. 

Using SQL function:

SELECT

ntUserID, dbo.udfUserCommentCount(ntUserID)

FROM tblUser

Execution Time: 34 Seconds

Using Procedure:

It is not possible to call stored procedure in the SELECT statement. For example:

SELECT ntUserID, dbo.uspUserCommentCount(ntUserID) FROM tblUser

It will throw error message: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.uspUserCommentCount", or the name is ambiguous.

To accomplish this task we must have to use either WHILE LOOP or CURSOR. Which will lead a row based approach. I think there is no need to explain that a row based approach can be how much costlier. 

DECLARE @ntUserID AS BIGINT

DECLARE @tblUserCommentCount TABLE(ntUserID BIGINT,ntCommentCount BIGINT)

DECLARE curUser CURSOR LOCAL FAST_FORWARD FOR SELECT ntUserID FROM tblUser

OPEN curUser

FETCH NEXT FROM curUser INTO @ntUserID

WHILE @@FETCH_STATUS = 0 BEGIN

     CREATE TABLE #tblCommentCount(ntCommentCount BIGINT)

    

     INSERT INTO #tblCommentCount

     EXECUTE dbo.uspUserCommentCount @ntUserID

    

     INSERT INTO @tblUserCommentCount

     SELECT @ntUserID,ntCommentCount FROM #tblCommentCount

     DROP TABLE #tblCommentCount

    

     FETCH NEXT FROM curUser INTO @ntUserID

    

END

CLOSE curUser

DEALLOCATE curUser

SELECT * FROM @tblUserCommentCount

Execution Time: 6 minutes 56 seconds

You might be also thinking that business need, can be full fill by very simple script. For example:

SELECT ntUserID,COUNT(*)

FROM tblComment

GROUP BY ntUserID

Then what is need to use SQL function or stored procedure. Yes there is no need to use those objects. In this example there was very simple business requirement. In case of complex business requirement some developers create stored procedure instead of SQL function and call the stored procedure in a LOOP either from application code or by using Transact-SQL. It may be due to either they not aware of query cost or they think it is very difficult to convert existing complex procedure to equivalent SQL function etc.

. 

 b. Create table valued SQL function when there is need to get a single result set  then output of result set is need to be used multiple times to perform some kind of join with other tables. For example: There is business need to get last three recent comments data of a user.  

Using stored procedure:

CREATE PROCEDURE uspGetLastThreeComment(

     @ntUserID BIGINT

)

AS

BEGIN

SELECT TOP(3) * FROM tblComment WHERE ntUserID = @ntUserID

ORDER BY dtCommentDate DESC

END

Using Table valued function:

CREATE FUNCTION udfGetLastThreeComment(

     @ntUserID BIGINT

)

RETURNS TABLE

AS

RETURN

     SELECT TOP(3) *

     FROM tblComment

     WHERE ntUserID = @ntUserID

  ORDER BY dtCommentDate DESC

If we execute only times then we will not get a significant performance difference:

EXECUTE dbo.uspGetLastThreeComment 10

Total logical read: 4

SELECT * FROM dbo.udfGetLastThreeComment(10)

Total logical read: 4

If there is business need last three comments of all users with user detail information by using existing procedure of tabled value function.

Using tabled value function:

SELECT *

FROM tblUser U CROSS APPLY

dbo.udfGetLastThreeComment(U.ntUserID)

Execution time: 39 Seconds

Using Procedure:

It is not possible to call stored procedure in JOIN statement. For example:

SELECT *

FROM tblUser U CROSS APPLY

dbo.uspGetLastThreeComment(U.ntUserID)

It will throw error message: Invalid object name 'dbo.uspGetLastThreeComment'.

To accomplish this task we must have to use either WHILE LOOP or CURSOR. Which will lead a row based approach and which a very costly operation.