SQL Server-聚焦计算列或计算列持久化查询性能（二十二）

前言

上一节我们详细讲解了计算列以及计算列持久化的问题，本节我们依然如前面讲解来看看二者查询性能问题，简短的内容，深入的理解，Always to review the basics。

持久化计算列比非持久化计算列性能要好

我们开始创建两个一样的表并都插入100条数据来进行比较，对于计算列我们重新进行创建计算列和非计算列持久化。

CREATE TABLE [dbo].[ComputeColumnCompare] (ID INT,
FirstName VARCHAR(100),
LastName CHAR(8000))
GO
INSERT INTO [dbo].[ComputeColumnCompare] (ID,FirstName,LastName)
SELECT TOP 100  ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

在ComputeColumn表上创建计算列

USE TSQL2012
GO

ALTER TABLE dbo.ComputeColumn ADD
FullName AS POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12)
GO

在ComputeColumnCompare表上创建计算持久化列

USE TSQL2012
GO

ALTER TABLE dbo.ComputeColumnCompare ADD
FullName_P AS POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12) PERSISTED
GO

此时我们来运行两个表对计算列和计算列持久化列的查询

USE TSQL2012
GO

SELECT FullName
FROM dbo.ComputeColumn
WHERE FullName = 531441
GO
SELECT FullName_P
FROM dbo.ComputeColumnCompare
WHERE FullName_P = 531441
GO

此时二者的开销是一样的，只是非持久化列多了一个Compute Scalar操作，主要是因为它计算值是在运行时，此时我们来看看操作成本。

我们看到二者性能还是有一点差异，所以我们能够知道如果计算操作比较复杂时利用持久化来提前进行计算性能会比非持久化列更好。是不是所有情况下持久化列性能都比持久化列性能要好呢？继续往下看。

非持久化计算列比持久化计算列性能要好

我们再来创建测试表并插入1万条数据来进行比较。

USE TSQL2012
GO

CREATE TABLE [dbo].[ComputeColumn] (ID INT,
FirstName VARCHAR(100),
LastName CHAR(800))
GO
CREATE TABLE [dbo].[ComputeColumnCompare](ID INT,
FirstName VARCHAR(100),
LastName CHAR(800))
GO
USE TSQL2012
GO

INSERT INTO  [dbo].[ComputeColumn](ID,FirstName,LastName)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
INSERT INTO [dbo].[ComputeColumnCompare](ID,FirstName,LastName)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

接下来在两表上创建持久化计算列和非持久化计算列

USE TSQL2012
GO

ALTER TABLE dbo.ComputeColumn ADD
FullName AS (FirstName+' '+LastName)
GO

ALTER TABLE dbo.ComputeColumnCompare ADD
FullName_P AS (FirstName+' '+LastName) PERSISTED
GO

最后我们进行查询看看查询计划结果

USE TSQL2012
GO

SELECT FullName
FROM dbo.ComputeColumn
WHERE FullName = 'Bob Smith'
GO
SELECT FullName_P
FROM dbo.ComputeColumnCompare
WHERE FullName_P = 'Bob Smith'
GO

 

到这里我们发现非持久化计算列性能要比持久化计算列性能要好，和上面对照的话我已经明确进行了标记定义列的大小以及插入行的多少是不同的，所以对于持久化列和非持久化列二者并没有绝对性能的谁好谁好，当我们想要看二者谁性能更佳时，我们可能需要考虑定义列的大小、数据行的多少等等。下面我们还看最后一种情况，就是在计算列上来创建索引。

非持久化计算列提高查询性能

我们继续创建测试表

USE TSQL2012
GO

CREATE TABLE [dbo].[ComputeColumn] (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100))
GO
CREATE TABLE [ComputeColumnCompare] (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100))
GO
USE TSQL2012
GO

INSERT INTO [dbo].[ComputeColumn] (ID,FirstName,LastName)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
INSERT INTO  [dbo].[ComputeColumnCompare](ID,FirstName,LastName)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

在ComputeColumn表上创建计算列并创建一个非聚集索引

ALTER TABLE dbo.ComputeColumn ADD
FullName AS (FirstName+' '+LastName)
GO

CREATE NONCLUSTERED INDEX IX_CompCol_CityTrim
ON dbo.ComputeColumn (FullName)
GO

在ComputeColumnCompare表上创建计算列

ALTER TABLE dbo.ComputeColumnCompare ADD
FullName_P AS (FirstName+' '+LastName)
GO

最后查询两个表看看查询计划结果

USE TSQL2012
GO

SELECT FullName
FROM dbo.ComputeColumn
WHERE FullName = 'Bob Smith'
GO
SELECT FullName_P
FROM dbo.ComputeColumnCompare
WHERE FullName_P = 'Bob Smith'
GO

 

从上述我们知道对计算列创建一个索引能很好的提高查询性能，当然了上述仅仅只是返回计算列，若返回其他列的话可能会导致Key Lookup，但是从另外一个角度来讲还是能提高查询性能，为了解决Key Lookup问题建立太多索引也是有问题的，具体情况具体分析吧。这里并没有比较持久化计算列和非持久化计算列的性能，二者其实是一样的，就没有比较了，只是在利用持久化在数据存储上不同而已。参考资料：【http://blog.sqlauthority.com/2010/08/03/sql-server-computed-column-persisted-and-performance/】

总结

到此我们算是结束了对于计算列以及关于计算列持久的概念和性能的分析，下节我们再看看其他查询的知识，接着就进入表表达式的学习，简短的内容，深入的理解，我们下节再会。

时间： 2024-08-04 00:16:58

SQL Server-聚焦计算列或计算列持久化查询性能（二十二）的相关文章

sql server 2005 select update 复杂计算 批量更新 悬赏100分

问题描述 sql server 2005 select update 复杂计算 批量更新 悬赏100分 sql server 2005 select update 复杂计算 批量更新 表结构如上图:ID,日期,代码,开盘,最高,最低,振幅3 求:前三日的振幅,并批量更新. 算法:代码相同,按日期排序,向前取三条,从最高里面找到最大的数,从最低里面找到最小的数. (最高 - 最低) / 开盘 = 振幅.开盘是当前行固定向前数三行的开盘值. 以更新第4条为例: (9 - 1) / 1 = 第4条的振

sql server合并另一个表的列

问题描述 sql server合并另一个表的列 现在我的数据库里有两个表 A表里面有城市,日期属性 B表里面有城市,日期,天气属性 A表里的日期是具体的,比如3月30日 B表里列举出了所有城市所有每一天的天气,比如从1月1日到5月1日的每一天是什么天气 现在我需要将B表里的天气属性合并到A表中 并且日期和城市对应 用sql语句怎么实现? 解决方案 select A.城市,A.日期,B.天气 from A left join B on A.城市 = B.城市 and A.日期 =B.日期 解决方案

SQL Server 2008 R2——PIVOT 行转列 以及聚合函数的选择

原文:SQL Server 2008 R2--PIVOT 行转列 以及聚合函数的选择 ==================================声明================================== 本文原创,转载在正文中显要的注明作者和出处,并保证文章的完整性. 未经作者同意请勿修改(包括本声明),保留法律追究的权利. 未经作者同意请勿用于学术性引用. 未经作者同意请勿用于商业出版.商业印刷.商业引用. 本文不定期修正完善,为保证内容正确,建议移步原文处阅读. 本文

SQL Server调优系列基础篇（子查询运算总结）

原文:SQL Server调优系列基础篇(子查询运算总结) 前言 前面我们的几篇文章介绍了一系列关于运算符的介绍,以及各个运算符的优化方式和技巧.其中涵盖:查看执行计划的方式.几种数据集常用的连接方式.联合运算符方式.并行运算符等一系列的我们常见的运算符.有兴趣的童鞋可以点击查看. 本篇我们介绍关于子查询语句的一系列内容,子查询一般是我们形成复杂查询的一些基础性操作,所以关于子查询的应用方式就非常重要. 废话少说,开始本篇的正题. 技术准备 数据库版本为SQL Server2008R2,利用微软

Sql Server优化之索引提示----我们为什么需要查询提示，Sql Server默认情况下优化策略选择的不足

原文:Sql Server优化之索引提示----我们为什么需要查询提示,Sql Server默认情况下优化策略选择的不足 环境: Sql Server2012 SP3企业版,Windows Server2008 标准版   问题由来: 最近在做DB优化的时候,发现一个存储过程有非常严重的性能问题, 由于整个SP整体逻辑是一个多表关联的复杂的查询,整体结构比较复杂的,通过的分析和尝试, 最后发现问题出在其中一个大表的查询上实现方式上, 因为这个大表上的意外的执行方式,导致其他表无法被驱动,其他表也

SQL Server中多表连接时驱动顺序对性能的影响

原文:SQL Server中多表连接时驱动顺序对性能的影响   本文出处:http://www.cnblogs.com/wy123/p/7106861.html (保留出处并非什么原创作品权利,本人拙作还远远达不到,仅仅是为了链接到原文,因为后续对可能存在的一些错误进行修正或补充,无他)   最近在SQL Server中多次遇到开发人员提交过来的有性能问题的SQL,其表面的原因是表之间去的驱动顺序造成的性能问题,具体表现在(已排除其他因素影响的情况下),存储过程偶发性的执行时间超出预期,甚至在调

sql server-请问在SQL SERVER里，有两个表的查询问题怎么解决

问题描述 请问在SQL SERVER里,有两个表的查询问题怎么解决 一个teacher表,一个stu_info表,现在要查询teacher表中每个教师教师名和的学生数 解决方案 join一下 select t.name, count(s.teacherid) from teachers t join stu_info on t.id = stu_info.teacherid where s.teacherid=t.name 解决方案二: SELECT tearcher.name, COUNT(

odbc-我用sql server 2008 通过ODBC 连接了sybase 后 查询出的数据中文是乱码

问题描述 我用sql server 2008 通过ODBC 连接了sybase 后 查询出的数据中文是乱码 我用sql server 2008 通过ODBC 连接了sybase 后 查询出的数据中文是乱码 解决方案 http://blog.sina.com.cn/s/blog_48e42dc90100i6ch.html 解决方案二: http://www.2cto.com/database/201203/122530.html

sql server实现在多个数据库间快速查询某个表信息的方法

本文实例讲述了sql server实现在多个数据库间快速查询某个表信息的方法.分享给大家供大家参考,具体如下: 最近出来实习,所在公司的服务器有十几个数据库,为了方便根据某个数据表的  表名  快速找到对应的数据库,又复习了一下游标的知识,写了下面这个sql代码,方便自己的工作. 1.先了解一下系统存储过程和系统表的使用,简单介绍一下我用到的几个系统存储过程(资料参考网络) use master --切换到系统数据库,因为下面用到的系统存储过程和系统表大部分存在于该数据库 go exec sp_