{"id":112,"date":"2008-08-12T23:13:17","date_gmt":"2008-08-12T21:13:17","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=112"},"modified":"2008-08-12T23:13:17","modified_gmt":"2008-08-12T21:13:17","slug":"views-or-functions","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2008\/08\/12\/views-or-functions\/","title":{"rendered":"Views or Functions?"},"content":{"rendered":"

Someone asked this question on SQLServerCentral<\/a>, I thought I’d post the test here for interest.<\/p>\n

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.<\/p>\n

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<\/p>\n

[source:SQL]<\/p>\n

SET NOCOUNT ON<\/p>\n

CREATE TABLE dbo.LargeTable2(
\nID int NOT NULL PRIMARY KEY,
\nSomeString char(6) NULL,
\nAGuid uniqueidentifier default newID()
\n)<\/p>\n

insert into LargeTable2 (id,SomeString)
\nSELECT ID, CHAR(FLOOR(RAND(ID*100)*13)+65)+CHAR(FLOOR(RAND(ID*800)*13)+65) + CHAR(FLOOR(RAND(ID*600)*13)+65) FROM (
\nSELECT top 500000 a.number*10000+b.number AS ID
\nfrom master..spt_values a cross join master..spt_values b
\nwhere a.name is null and b.name is null) x<\/p>\n

CREATE index idx_someString on LargeTable2 (SomeString)
\ngo<\/p>\n

CREATE TABLE dbo.LargeTable3(
\nID int identity NOT NULL PRIMARY KEY,
\nSomeString char(6) NULL,
\nTheDate DATETIME,
\n)
\nGO
\ninsert into LargeTable3 (SomeString)
\nSELECT distinct SomeString FROM LargeTable2
\nGO<\/p>\n

UPDATE LargeTable3 SET TheDate = DATEADD(hh,ID,’1980\/01\/01′) where TheDate IS NULL
\nGO<\/p>\n

insert into LargeTable3 (SomeString)
\nSELECT distinct SomeString FROM LargeTable2
\nGO<\/p>\n

UPDATE LargeTable3 SET TheDate = DATEADD(hh,ID,’2000\/01\/01′) where TheDate IS NULL
\nGO<\/p>\n

insert into LargeTable3 (SomeString)
\nSELECT distinct SomeString FROM LargeTable2
\nGO<\/p>\n

UPDATE LargeTable3 SET TheDate = DATEADD(hh,ID,’2080\/01\/01′) where TheDate IS NULL
\nGO<\/p>\n

CREATE index idx_someString on LargeTable3 (SomeString)
\ngo<\/p>\n

[\/source]<\/p>\n

Now, the view, the procedure and the functions<\/p>\n

[source:SQL]<\/p>\n

CREATE VIEW Test1 AS
\nSELECT ID, SomeString, AGuid FROM LargeTable2
\nGO<\/p>\n

— Inline function
\nCREATE FUNCTION Testing2 (@StringParam char(6))
\nRETURNS TABLE — inline table valued function
\nAS
\nRETURN SELECT ID, SomeString FROM LargeTable2 — from the table
\nWHERE SomeString = @StringParam
\nGO<\/p>\n

— multi-statement function
\nCREATE FUNCTION Testing3 (@StringParam char(6))
\nRETURNS @desiredRows TABLE
\n(
\nID int primary key NOT NULL,
\nSomeString char(6)
\n)
\nAS
\nBEGIN
\nINSERT INTO @desiredRows (ID, SomeString)
\nSELECT ID, SomeString FROM LargeTable2 — from the table
\nWHERE SomeString = @StringParam
\nRETURN
\nEND
\nGO<\/p>\n

— Procedure filtering on the view.
\nCREATE PROCEDURE Testing4 @StringParam char(6) AS
\nSELECT Test1.ID, Test1.SomeString, LargeTable3.TheDate FROM Test1 — from the view
\ninner join LargeTable3 on Test1.SomeString = LargeTable3.SomeString
\nWHERE Test1.SomeString = @StringParam
\nGO<\/p>\n

[\/source]<\/p>\n

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.<\/p>\n

[source:SQL]<\/p>\n

SET STATISTICS IO ON
\nGO
\nSET STATISTICS TIME ON
\nGO
\nSET NOCOUNT OFF
\n— First the procedure<\/p>\n

EXEC Testing4 ‘CKK’
\nGO<\/p>\n

— the single statement UDF<\/p>\n

SELECT T2.ID, T2.SomeString, LargeTable3.TheDate FROM Testing2(‘CKK’) T2
\ninner join LargeTable3 on T2.SomeString = LargeTable3.SomeString<\/p>\n

— the multi statement UDF<\/p>\n

SELECT T3.ID, T3.SomeString, LargeTable3.TheDate FROM Testing3(‘CKK’) T3
\ninner join LargeTable3 on T3.SomeString = LargeTable3.SomeString<\/p>\n

SET STATISTICS IO OFF
\nGO
\nSET STATISTICS TIME OFF
\nGO<\/p>\n

[\/source]<\/p>\n

Procedure:
\n(10938 row(s) affected)
\nTable ‘LargeTable2’. Scan count 3, logical reads 33
\nTable ‘LargeTable3’. Scan count 1, logical reads 4<\/p>\n

SQL Server Execution Times:
\nCPU time = 0 ms, elapsed time = 121 ms.<\/p>\n

Inline UDF:
\n(10938 row(s) affected)
\nTable ‘LargeTable2’. Scan count 3, logical reads 33
\nTable ‘LargeTable3’. Scan count 1, logical reads 4<\/p>\n

SQL Server Execution Times:
\nCPU time = 0 ms, elapsed time = 145 ms.<\/p>\n

Multi-statement UDF:
\n(10938 row(s) affected)
\nTable ‘LargeTable3’. Scan count 3646, logical reads 29168
\nTable ‘#45BE5BA9’. Scan count 1, logical reads 11<\/p>\n

SQL Server Execution Times:
\nCPU time = 78 ms, elapsed time = 486 ms.<\/p>\n

More or less as expected.<\/p>\n","protected":false},"excerpt":{"rendered":"

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… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[25,15],"tags":[],"class_list":["post-112","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-1O","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/112","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=112"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/112\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=112"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=112"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=112"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}