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

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

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

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

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

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

详情如下:

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

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

例子:

a. 创建TVF:

1: use [MyDemo]
   2: go
   3: alter FUNCTION Dep_Salaries1
   4: (
   5: @empid int
   6: )
   7: RETURNS @table table
   8: (
   9: Department int,
  10: Salary_Max int,
  11: Salary_Min int
  12: )
  13: AS
  14: BEGIN
  15: declare @Department int = (select S.deptid from Employees s where s.empid=@empid)
  16: insert into @table
  17: 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
  18: RETURN
  19: END
  20: GO

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

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

c. 使用临时表代替TVF:

1: go
   2: alter procedure Performant_SP1
   3: @empid int
   4: as
   5: begin
   6: create table #table
   7: (
   8: Department int,
   9: Salary_Max int,
  10: Salary_Min int
  11: )
  12: create clustered index #table_index1 on #table (Department)
  13: insert into #table select * from Dep_Salaries1 (@empid )
  14: select T.deptid as department_name , s.* from #table S inner join Departments T ON S.Department =T.deptid
  15: 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. 创建标量函数:

1: use [Workshops]
   2: go
   3: create FUNCTION Salary_Tax
   4: (
   5: @empid int
   6: )
   7: RETURNS float
   8: AS
   9: BEGIN
  10: declare @salary int = (select (S.salary-100) from Employees s where s.empid=@empid)
  11: RETURN @salary
  12: END
  13: GO
  14: --性能低些的标量函数
  15: Select empid ,dbo.Salary_Tax (empid) as 'SalaryWithTax' from Employees

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

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

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

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

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

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

上班时间到了!

期待下一篇吧!

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏晨星先生的自留地

mysql注入高级篇3--报错注入

21940
来自专栏GreenLeaves

使用group by rollup和group by cube后的辅助函数

本文主要介绍,报表在使用group by rollup和group by cube后的辅助函数。 CREATE TABLE TEST8 ( "ID...

26870
来自专栏Alan's Lab

[Coursera][From Nand to Tetris / Part I] 第六周 汇编器项目 python 实现

今天折腾一上午,终于 完成了 Coursera 上 From Nand to Tetris / Part I 这个课程的最后一个汇编器项目。这套课程真是没白跟,...

15830
来自专栏芋道源码1024

数据库分库分表中间件 Sharding-JDBC 源码分析 —— SQL 改写

本文主要基于 Sharding-JDBC 1.5.0 正式版 1. 概述 2. SQLToken 3.SQL 改写 3.4.1 分页补充 3.1 TableTo...

46460
来自专栏工科狗和生物喵

【计算机本科补全计划】Mysql 学习小计(2)

正文之前 昨天下午写了篇 Mysql学习小计,结果出乎意料的受欢迎?变相刺激了我多写点 Mysql?好吧,如尔所愿。我晚上反正还不知道学点啥,就把今天看的那个菜...

371110
来自专栏坚毅的PHP

mysql数据迁移hbase问题

无法直接dump,写了java多线程程序做迁移 问题1:Operation not allowed after ResultSet closed 裸jdbc语句...

40450
来自专栏铭毅天下

干货 | 通透理解Elasticsearch聚合

使用Elasticsearch的过程中,除了全文检索,或多或少会做统计操作,而做统计操作势必会使用Elasticsearch聚合操作。

39230
来自专栏Kevin-ZhangCG

数据库索引

索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中,索引也允许数据库...

12400
来自专栏ml

mysql知识初篇(一)

mysql介绍 (1) mysql数据库是瑞典AB开发。 (2) mysql--> sun --> oracle。 (3) mysql数据库的...

36370
来自专栏恰童鞋骚年

Hadoop学习笔记—8.Combiner与自定义Combiner

  在第四篇博文《初识MapReduce》中,我们认识了MapReduce的八大步凑,其中在Map阶段总共五个步骤,如下图所示:

12310

扫码关注云+社区

领取腾讯云代金券