Technical: Microsoft – SQL Server / .Net & Entity Framework – Table Value Function (TVF)

Technical: Microsoft – SQL Server / .Net & Entity Framework – Table Value Function (TVF)

I have a situation where I need to quickly optimize a new system.  And, so as I dig in I notice that quite a few of the entities are Views.

In yesteryears, some people prefer Table Value Function (TVF) to views as TVF supports the ability to use parameters \ passed in arguments inside their definition.

A views offers a very generalized entity.

Through the use of these parameters one is able to better lead the Query Engine and allow it to “fast track” knowing that it can optimize around a single parameter.

A simplified model is shown below:



use [tempdb]
go

if OBJECT_id('dbo.v_student') is not null
begin
	drop view dbo.v_student
end	
go

if OBJECT_id('dbo.student') is not null
begin
	drop table dbo.student
end	
go

if OBJECT_id('dbo.fn_student_zipcode') is not null
begin
	drop function dbo.fn_student_zipcode
end	
go

create table dbo.student
(
	  [id] int not null identity(1,1) 
	, firstname sysname not null
	, lastname sysname not null
	, zipcode varchar(10) null

	, fullname as firstname + ' ' + lastname

	, constraint PK_Student primary key ([id])

)
go

create view dbo.v_student
as

	select 
			  id
			, firstname
			, lastname
			, fullName
	from   dbo.student
go	

create function dbo.fn_student_zipcode
(
	@zipcode varchar(10)
)
returns table
as
	return
	(
		select 
				  id
				, firstname
				, lastname
				, fullName
				, zipcode
		from   dbo.student
		where  zipcode like (@zipcode + '%')
	)		
go	

Hopefully, from code snippet shown above who is able to see that a view is suitable for all generic use-cases.

And, that a TVF might be more appropriate for specific requests.

In our test where we have the old model of just a view and one that is a bit more rigorously defined using a Table defined function, we were able to better our timeline, a tiny bit.

View



(0 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableA'. Scan count 0, logical reads 2694, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableB'. Scan count 3109, logical reads 9520, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableC'. Scan count 2272, logical reads 4546, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableD'. Scan count 0, logical reads 1611, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableE'. Scan count 568, logical reads 1617, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableF'. Scan count 568, logical reads 1136, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableG'. Scan count 0, logical reads 1661, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableH'. Scan count 568, logical reads 1754, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableI'. Scan count 4, logical reads 2079, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableJ'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableK'. Scan count 4, logical reads 1722, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 374 ms,  elapsed time = 404 ms.

Table Value Function (TVF)



(0 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableA'. Scan count 0, logical reads 2694, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableB'. Scan count 3676, logical reads 11353, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableC'. Scan count 2272, logical reads 4546, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableD'. Scan count 0, logical reads 1611, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableE'. Scan count 568, logical reads 1617, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableF'. Scan count 568, logical reads 1136, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableG'. Scan count 0, logical reads 1661, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableH'. Scan count 568, logical reads 1754, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableI'. Scan count 4, logical reads 2079, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableJ'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableK'. Scan count 4, logical reads 1722, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 234 ms,  elapsed time = 355 ms.

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

Here is a little comparison:

Scale View Function (TVF)
Query cost relative to the batch 54% 46%
SQL Server Execution Time 374 ms 234 ms

As our corporate chief Architect and I discussed last night, we had a period of brief lamentation on how TVF is only recently supported in Microsoft Entity Framework.

Continue Reading and watching

Reading

Video

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s