SQL Server 性能优化之——T-SQL TVF和标量函数

阅读导航

1. TVF(表-值行数Table-Valued Functions)

        a. 创建TVF

        b. 使用TVF的低性能T-SQL

        c. 使用临时表代替TVF

2. 标量函数

3. 替代标量函数

    1). 临时表

    2). 持久化确定的计算列

    3). 使用计划更新工作

        a. 创建标量函数

        b. 使用临时表替换标量函数

        c. 使用持久化确定的计算列

        d. 使用计划工作代替标量函数

 

上一篇介绍了关于“临时表、表变量和Union优化”这次转向关注定义函数——也就是表-值函数、标量函数。

UDF（用户定义函数，User defined Function）对于集中精力处理业务逻辑很方便，因为可以在UDF中指定一组业务逻辑，其中可以设计多个存储过程和一些特定的查询语句。但是，由于UDF对CPU的大量请求可能导致性能下降

 

1. TVF(表-值行数Table-Valued Functions)

一般情况，当使用TVF与一个对象内联接，如果该对象没有索引将会导致TVF像索引扫描或表扫描一样做扫描操作。

作为一个选择，可以创建临时表，临时表上创建适当的聚集索引或非聚集索引。

详情如下：

• 创建适当的临时表。
• 根据T-SQL创建适当的聚集索引和非聚集索引。
• 将TVF的数据插入到临时表中。
• 用临时表和相关的列替换每一个TVF。
• 在查询语句执行结束后，删除临时表。

                注意，临时表的性能提升是超过表参数，在上一篇博客中提到的，表参数不支持索引。

例子：

a. 创建TVF：

use [MyDemo]
go
alter FUNCTION Dep_Salaries1
(
@empid int
)
RETURNS @table table
(
Department int,
Salary_Max int,
Salary_Min int
)
AS
BEGIN
declare @Department int = (select S.deptid from Employees s where s.empid=@empid)
insert into @table
SELECT S.deptid , max (Salary) , MIN(Salary) FROM Employees s inner join Departments T ON S.deptid =T.deptid group by S.deptid having S.deptid =@Department
RETURN
END
GO

b. 使用TVF的低性能T-SQL：

alter procedure Unperformant_SP1
@empid int
as
begin
select T.deptid as department_name , s.* from Dep_Salaries1 (@empid )S inner join Departments T ON S.Department =T.deptid
end

 

c. 使用临时表代替TVF：

go
alter procedure Performant_SP1
@empid int
as
begin
create table #table
(
Department int,
Salary_Max int,
Salary_Min int
)
create clustered index #table_index1 on #table (Department)
insert into #table select * from Dep_Salaries1 (@empid )
select T.deptid as department_name , s.* from #table S inner join Departments T ON S.Department =T.deptid
end

在使用具体的查询和数据时，还是应该进行必要的性能测试，发现最适合自己情况的解决方案。

2. 标量函数

标量函数，对于确定存储过程或特定查询语句的聚合值、累计值、差分值非常方便的，但是对性能是有损失的，尤其使用大数据，标量函数将执行每一个记录。

 

3. 替代标量函数

1). 临时表

使用临时表，但是这个解决方案有一点不同于TVF的情况，这里希望完全放弃标量函数并且也不去直接使用内部T-SQL代码。

2). 持久化确定的计算列

持久化确定的计算列值不是每次选择都重新计算该列，而只是在创建时计算一次。因此，这时可以添加不同的T-SQL语句提高性能，因为这样可以减少进程的开销。

这个功能可以通过下面步骤添加：

• 增加一个新的计算列存储标量函数的结果。
• 启用这个计算列的持久化功能。
• 在列（不管是主键列还是包含列）上设置适当的索引。

             但是要注意持久化功能还是有一些限制，如：

                   i. 计算列不应该使用任何其他记录的聚合功能。

                   ii. 计算列不应该使用调用外部系统过程的功能。

                   iii. 计算列不应该使用任何其他表的其他字段的功能。

                   iv. 计算列生成最好是使用系统提供的功能，例如：Convert、Cast、Replace等等，并且开发者不能创建UDF，因为UDF通常和该功能相矛盾。

这仅仅是适用于持久化的功能，但是可以添加计算列索引，应该通过确定计算数据的精确类型（如，INT、 Bigint、 DateTime和decimal）精确列的类型。如果数据类型不精确，可以添加这些列为索引的包含列的一部分，但不是主键列的一部分。

3). 使用计划更新工作

如果不可能使用持久化确定的计算列，可以创建普通列并同时创建计划更新工作，更新这些列的标量函数输出，然后用T-SQL代替标量函数并且在T-SQL中使用这些列。具体如下：

a. 创建标量函数：

use [Workshops]
go
create FUNCTION Salary_Tax
(
@empid int
)
RETURNS float
AS
BEGIN
declare @salary int = (select (S.salary-100) from Employees s where s.empid=@empid)
RETURN @salary
END
GO
--性能低些的标量函数
Select empid ,dbo.Salary_Tax (empid) as 'SalaryWithTax' from Employees

 

b. 使用临时表替换标量函数：

Create Table #temp (Empid int primary key clustered , Salary_Tax float)
Create nonclustered index #temp_Index1 on #temp (Empid ) include (Salary_Tax )
insert into #temp select Empid ,(Salary-100) as salary_Tax from Employees
select * from #temp

 

c. 使用持久化确定的计算列：

ALTER TABLE dbo.Employees ADD Salary_Tax AS Salary-100 PERSISTED
Create nonclustered index Employees_Index1 on Employees (Empid, Salary_Tax )
select empid ,Salary_Tax from Employees

 

d. 使用计划工作代替标量函数：

ALTER TABLE dbo.Employees ADD Salary_Tax1 float, update_flag bit
ALTER TABLE dbo.Employees ADD CONSTRAINT DF_Employees_update_flag DEFAULT 0 FOR update_flag
Schedule the below DML update by an appropriate frequency according to your workload
Update Employees set Salary_Tax1=Salary-100 WHERE UPDATE_Flag=0
Then you can include the below select query within your stored procedure.
select empid , Salary_Tax1 from Employees

 

上班时间到了！

期待下一篇吧！

任何的优化的不是绝对的，只有适应自己环境才是最好的，性能测试是必要。