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

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 前言 上一节我们详细讲解了计算列以及计算列持久化的问题,本节我们依然如前面讲解来看看二者查询性能问题,简短的内容,深入的理解,Always to review the basics。 持久化计算列比非持久化计算列性能要好 我们开始创建两个一样的表并都插入100条数据来进行比较,对于计算列我们重新进行创建计算列和非计算列持久化。

前言

上一节我们详细讲解了计算列以及计算列持久化的问题,本节我们依然如前面讲解来看看二者查询性能问题,简短的内容,深入的理解,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/

总结

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

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
5月前
|
分布式计算 BI MaxCompute
SQL 能力问题之输出聚合的维度列的名称,如何解决
SQL 能力问题之输出聚合的维度列的名称,如何解决
|
7月前
|
SQL 数据库
SQL标识列实现自动编号的步骤和技巧以及优势
SQL标识列实现自动编号的步骤和技巧以及优势
148 0
|
SQL 测试技术 Go
SQL Server利用HashKey计算列解决宽字段查询的性能问题
#SQL Server利用HashKey计算列解决宽字段查询的性能问题 ##主人翁        本文主人翁:MSSQL菜鸟和MSSQL老鸟。 ##问题提出        某年某月某日,某MSSQL菜鸟满脸愁容的跑到老鸟跟前,心灰意懒的对老鸟说“我最近遇到一个问题,很大的问题,对,非常大的问题”。老鸟不急不慢的
3712 0
|
SQL 算法 索引
SQL Server 执行计划利用统计信息对数据行的预估原理二(为什么复合索引列顺序会影响到执行计划对数据行的预估)
原文:SQL Server 执行计划利用统计信息对数据行的预估原理二(为什么复合索引列顺序会影响到执行计划对数据行的预估)   本文出处:http://www.cnblogs.com/wy123/p/6008477.html    关于统计信息对数据行数做预估,之前写过对非相关列(单独或者单独的索引列)进行预估时候的算法,参考这里。
1050 0
|
SQL 存储 索引
sql server 索引阐述系列七 索引填充因子与碎片
原文:sql server 索引阐述系列七 索引填充因子与碎片 一.概述     索引填充因子作用:提供填充因子选项是为了优化索引数据存储和性能。 当创建或重新生成索引时,填充因子的值可确定每个叶级页上要填充数据的空间百分比,以便在每一页上保留一些剩余存储空间作为以后扩展索引的可用空间,例如:指定填充因子的值为 80 表示每个叶级页上将有 20% 的空间保留为空,以便随着向基础表中添加数据而为扩展索引提供空间。
1126 0
|
SQL 数据库 Go
使用sql语句创建修改SQL Server标识列(即自动增长列)
原文:使用sql语句创建修改SQL Server标识列(即自动增长列) 一、标识列的定义以及特点SQL Server中的标识列又称标识符列,习惯上又叫自增列。该种列具有以下三种特点:1、列的数据类型为不带小数的数值类型2、在进行插入(Insert)操作时,该列的值是由系统按一定规律生成,不允许空值3、列值不重复,具有标识表中每一行的作用,每个表只能有一个标识列。
2083 0