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.
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.
No comments:
Post a Comment