关于表值函数的性能消耗

--create function

alter function dbo.fn_Test
(  
 @VendorNumber varchar(10) ,  
 @ItemNumber varchar(10)  
)  
RETURNS @SharedItemList TABLE  
(ItemNumber CHAR(25), SharedItemNumber CHAR(25))  
AS  
BEGIN  
INSERT INTO @SharedItemList(ItemNumber, SharedItemNumber)  
select @VendorNumber+'a1',@ItemNumber+'b1'
union all
select @VendorNumber+'a2',@ItemNumber+'b2'
union all
select @VendorNumber+'a3',@ItemNumber+'b3'
union all
select @VendorNumber+'a4',@ItemNumber+'b4'

delete top (1)  @SharedItemList where ItemNumber like '%a1%'
 RETURN;  

end

--create temp table and insert date

create table dbo.tony_Test 
(id int identity(1,1) primary key,name varchar(10),adds varchar(10))

declare @i int =0
while (@i<=1000)
begin
	set @i=@i+1
	insert into  dbo.tony_Test  (name,adds)
	select 'wowu'+cast(@i as varchar(5)),'yuyan'+cast(@i as varchar(5))
end

--查询语句

set statistics io on 
set statistics profile on 
select a2.* from   dbo.tony_Test  as a1 with(nolock)
cross apply 
dbo.fn_Test(a1.name,a1.adds) as a2
set statistics io off
set statistics profile off

--request
(3003 row(s) affected)
Table '#1FCDBCEB'. Scan count 1001, logical reads 1001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tony_Test'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Rows                 Executes             StmtText                                                                                                      StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                      DefinedValues                               EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                  Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------- ------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
3003                 1                    select a2.* from   dbo.tony_Test  as a1 with(nolock)
cross apply 
dbo.fn_Test(a1.name,a1.adds) as a2        2003        1           0           NULL                           NULL                           NULL                                                                          NULL                                        1001          NULL          NULL          NULL        0.01327214       NULL                                        NULL     SELECT                                                           0        NULL
3003                 1                      |--Nested Loops(Inner Join, OUTER REFERENCES:([a1].[name], [a1].[adds]))                                    2003        2           1           Nested Loops                   Inner Join                     OUTER REFERENCES:([a1].[name], [a1].[adds])                                   NULL                                        1001          0             0.00418418    57          0.01327214       [a2].[ItemNumber], [a2].[SharedItemNumber]  NULL     PLAN_ROW                                                         0        1
1001                 1                           |--Clustered Index Scan(OBJECT:([master].[dbo].[tony_Test].[PK__tony_Tes__3213E83F567ED357] AS [a1]))  2003        3           2           Clustered Index Scan           Clustered Index Scan           OBJECT:([master].[dbo].[tony_Test].[PK__tony_Tes__3213E83F567ED357] AS [a1])  [a1].[name], [a1].[adds]                    1001          0.006828704   0.0012581     26          0.008086803      [a1].[name], [a1].[adds]                    NULL     PLAN_ROW                                                         0        1
3003                 1001                        |--Table-valued function(OBJECT:([master].[dbo].[fn_Test] AS [a2]))                                    2003        4           2           Table-valued function          Table-valued function          OBJECT:([master].[dbo].[fn_Test] AS [a2])                                     [a2].[ItemNumber], [a2].[SharedItemNumber]  1             0             1.157E-06     57          0.001001157      [a2].[ItemNumber], [a2].[SharedItemNumber]  NULL     PLAN_ROW                                                         0        1001
 

select name from tempdb.sys.tables 
where name ='#1FCDBCEB'
--request
name
--------------------------------------------------------------------------------------------------------------------------------
#1FCDBCEB

我们可以看到cross apply产生了一个临时表，并且写入行数和读取行数与tony_test表行数一致。

说明使用表值函数时，tempdb下会产生一个#类型的临时表，把表值函数产生的数据都插入这个临时表中，并使得这个临时表与tony_test进行连接。

由此我们可以得出结论，表值函数的应用实际就是在tempdb中创建一个临时表，并插入类似SP的查询结果一样的结果集到里面。

但这样性能并不佳，这样直接后果有两个

1、系统产生的临时表并无索引，数据量大后依然采用表扫描会产生性能问题

2、系统产生的临时表只有系统自己释放。若这个表足够大，将持续较长时间占用系统资源

所以我们还是建议大多数情况下采用sp+临时表的方式代替表值函数功能。

除非需求必要并且数据量足够小，才可以考虑表值函数的应用。