Views or Functions?

Someone asked this question on SQLServerCentral, I thought I’d post the test here for interest.

The question was on which would execute faster, creating a view, then creating a procedure that accepted a parameter and filtered the view, or creating a table valued function that took a parameter.

From testing, the answer is the procedure, but not by much. A inline table-valued-function comes a very close second and the multi-statement table-valued-function waddles in last. Not really surprising. Here’s the (fairly simplistic) test code for that in case anyone wants to try it out. First a lot of setup

[source:SQL]

SET NOCOUNT ON

CREATE TABLE dbo.LargeTable2(
ID int NOT NULL PRIMARY KEY,
SomeString char(6) NULL,
AGuid uniqueidentifier default newID()
)

insert into LargeTable2 (id,SomeString)
SELECT ID, CHAR(FLOOR(RAND(ID*100)*13)+65)+CHAR(FLOOR(RAND(ID*800)*13)+65) + CHAR(FLOOR(RAND(ID*600)*13)+65) FROM (
SELECT top 500000 a.number*10000+b.number AS ID
from master..spt_values a cross join master..spt_values b
where a.name is null and b.name is null) x

CREATE index idx_someString on LargeTable2 (SomeString)
go

CREATE TABLE dbo.LargeTable3(
ID int identity NOT NULL PRIMARY KEY,
SomeString char(6) NULL,
TheDate DATETIME,
)
GO
insert into LargeTable3 (SomeString)
SELECT distinct SomeString FROM LargeTable2
GO

UPDATE LargeTable3 SET TheDate = DATEADD(hh,ID,’1980/01/01′) where TheDate IS NULL
GO

insert into LargeTable3 (SomeString)
SELECT distinct SomeString FROM LargeTable2
GO

UPDATE LargeTable3 SET TheDate = DATEADD(hh,ID,’2000/01/01′) where TheDate IS NULL
GO

insert into LargeTable3 (SomeString)
SELECT distinct SomeString FROM LargeTable2
GO

UPDATE LargeTable3 SET TheDate = DATEADD(hh,ID,’2080/01/01′) where TheDate IS NULL
GO

CREATE index idx_someString on LargeTable3 (SomeString)
go

[/source]

Now, the view, the procedure and the functions

[source:SQL]

CREATE VIEW Test1 AS
SELECT ID, SomeString, AGuid FROM LargeTable2
GO

— Inline function
CREATE FUNCTION Testing2 (@StringParam char(6))
RETURNS TABLE — inline table valued function
AS
RETURN SELECT ID, SomeString FROM LargeTable2 — from the table
WHERE SomeString = @StringParam
GO

— multi-statement function
CREATE FUNCTION Testing3 (@StringParam char(6))
RETURNS @desiredRows TABLE
(
ID int primary key NOT NULL,
SomeString char(6)
)
AS
BEGIN
INSERT INTO @desiredRows (ID, SomeString)
SELECT ID, SomeString FROM LargeTable2 — from the table
WHERE SomeString = @StringParam
RETURN
END
GO

— Procedure filtering on the view.
CREATE PROCEDURE Testing4 @StringParam char(6) AS
SELECT Test1.ID, Test1.SomeString, LargeTable3.TheDate FROM Test1 — from the view
inner join LargeTable3 on Test1.SomeString = LargeTable3.SomeString
WHERE Test1.SomeString = @StringParam
GO

[/source]

So, how did they run? Bear in mind, these tests were run on a desktop and the actual times will vary depending on what hardware is used and what else is running.

[source:SQL]

SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
SET NOCOUNT OFF
— First the procedure

EXEC Testing4 ‘CKK’
GO

— the single statement UDF

SELECT T2.ID, T2.SomeString, LargeTable3.TheDate FROM Testing2(‘CKK’) T2
inner join LargeTable3 on T2.SomeString = LargeTable3.SomeString

— the multi statement UDF

SELECT T3.ID, T3.SomeString, LargeTable3.TheDate FROM Testing3(‘CKK’) T3
inner join LargeTable3 on T3.SomeString = LargeTable3.SomeString

SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO

[/source]

Procedure:
(10938 row(s) affected)
Table ‘LargeTable2’. Scan count 3, logical reads 33
Table ‘LargeTable3’. Scan count 1, logical reads 4

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 121 ms.

Inline UDF:
(10938 row(s) affected)
Table ‘LargeTable2’. Scan count 3, logical reads 33
Table ‘LargeTable3’. Scan count 1, logical reads 4

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 145 ms.

Multi-statement UDF:
(10938 row(s) affected)
Table ‘LargeTable3’. Scan count 3646, logical reads 29168
Table ‘#45BE5BA9’. Scan count 1, logical reads 11

SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 486 ms.

More or less as expected.