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.

9 Comments

  1. Saggi Neumann

    Hi Gail,

    Interesting post supporting what we usually think&tell the customers.

    You left out the parse&compile times on these executions. Were there any differences?

    Thanks,
    Saggi

    Reply
  2. Gail

    I don’t recall there been high compile times. That said, I run queries a couple times before taking test times, to prime the cache, and so I wasn’t paying much attention to the times in the first execution.

    Reply
  3. Pingback: More on Table Valued Functions « Home of the Scary DBA

  4. Adam Haines

    Gail,

    Why do you say the stored procedure is a more effcient query, if the inline TVF has the same IO and CPU? Elasped time is not an accurate number for testing performance because it varies from run to run and is influenced by the load your server is undertaking. CPU is the main thing to look at in statistics TIME because it is a more consistent measure to how much CPU resources were actually used.

    I know there is a fine line here between the two, but I would say the SP and the inline TVF are equal.

    Reply
  5. Gail

    iirc, the elapsed time for the UDF was always slightly higher. I admit, it’s very, very close, and should probably be considered equivalent

    Also, if you check the exec plan, SQL considers the UDF to be much more expensive (in my test 10% for the proc, 90% for the UDF), though that’s probably not actually accurate. I did notice that the optimiser can’t see a join predicate for the join with the UDF, which may have side effects.

    Reply
  6. jimbobmcgee

    >> I did notice that the optimiser can’t see a join predicate for the join with the UDF, which may have side effects.

    Does this mean that, even if a source table is indexed, joining on an inline TVF that only selects from that source table will not use the index?

    J.

    Reply
  7. Gail

    I’m not sure. I’ll test it out and see.

    Reply
  8. Chris

    We have a situation where we are using a inline table valued function that performs terribly when the server is started. If you “ALTER index ALL” on any of it’s dependent tables then the performance is cut from 30sec down to 2 sec. Would you have any insight for me?

    Reply
  9. Gail

    Almost sounds like parameter sniffing. An alter index would force a recompile.

    Hard to say much more without seeing it. Are you a member of the SQLServerCentral forums?

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *