最近发现项目中的 SQL 的 WHERE 部分有很多的计算组成的查询条件,例如:
- USE AdventureWorks2014
- SELECT 1 FROMdbo.PersonWHEREFirstName+' '+LastName='Diane Margheim'
因此想要针对这个部分进行下优化,所以打算采用计算列的方式,但是又没有证明到底可不可行,所以发表下该文章发表下自己的理解也希望各位大神能点出不足的地方.
以下是针对计算列性能优化的证明:
先填充数据
- USE AdventureWorks2014
- GO
- SELECT * INTOdbo.personFROM Person.Person
- GO
第一种情况,无计算列情况
- SET STATISTICSIOON
- SET STATISTICSTIMEON
- DBCC FREEPROCCACHE
- DBCC DROPCLEANBUFFERS
- GO
- SELECT 1 FROMdbo.PersonWHEREFirstName+' '+LastName='Diane Margheim'
- GO
第二种情况,添加计算列
- ALTER TABLEdbo.PersonADDfullNameAS(FirstName+' '+LastName)
- SET STATISTICSIOON
- SET STATISTICSTIMEON
- DBCC FREEPROCCACHE
- DBCC DROPCLEANBUFFERS
- GO
- SELECT 1 FROMdbo.PersonWHEREFirstName+' '+LastName='Diane Margheim'
- GO
再次查询
- SET STATISTICSIOON
- SET STATISTICSTIMEON
- DBCC FREEPROCCACHE
- DBCC DROPCLEANBUFFERS
- GO
- SELECT 1 FROMdbo.PersonWHEREFirstName+' '+LastName='Diane Margheim'
- GO
从信息和查询计划可得知,添加计算列以后不会带来性能上的提升
第三种情况,给计算列设置为持久,再次尝试
- ALTER TABLEdbo.PersonADDfullNameAS(FirstName+' '+LastName) PERSISTED
- SET STATISTICSIOON
- SET STATISTICSTIMEON
- DBCC FREEPROCCACHE
- DBCC DROPCLEANBUFFERS
- GO
- SELECT 1 FROMdbo.PersonWHEREFirstName+' '+LastName='Diane Margheim'
- GO
将计算列设置可持久化以后效能会有一个提升(预读 15 次)
第四种情况,不持久化的情况下给计算列添加索引
- CREATE INDEXix_index_fullNameONdbo.Person(fullName)
- SET STATISTICSIOON
- SET STATISTICSTIMEON
- DBCC FREEPROCCACHE
- DBCC DROPCLEANBUFFERS
- GO
- SELECT 1 FROMdbo.PersonWHEREFirstName+' '+LastName='Diane Margheim'
- GO
从讯息可得知,添加了索引以后,查询提高一个数量级,
第五种情况,持久化的情况下给计算列添加索引
已经对性能提高微乎其微了。
所以针对以上五种情况可得知:
计算列(持久化或者不持久化)添加索引以后可以对 SQL 性能有所提高。
PS:持久化会增大本地的物理存储空间
以上是我对计算列性能提高所带来的证明,如果有不足,还请指点,谢谢:)
来源: http://www.cnblogs.com/blog-east/p/6916701.html