首页 > 代码库 > SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句
SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句
先看常用的一种表结构设计方式：
那么可能会遇到一种典型的查询方式，主子表关联，查询子表中的某些（或者全部）Key点对应的Value，横向显示（也即以行的方式显示）
这种查询方式很明显的一个却显示多次对字表查询（暂时抛开索引）
相比这种查询方式很多人都遇到过，如果子表是配置信息之类的小表的话，问题不大，如果字表数据量较大，可能就会有影响了。
这个查询目的是将”纵表”存储的结果“横向”显示，相当于横列转换的感觉了。
可以将子表的结果一次性将纵表的结果转换成横标，再跟主表连接，
然后得到一个最终一样的查询结果（格式），就能够减少子表的查询次数
这里将子表的结果“一次性将纵表的结果转换成横标”，是典型的行列转换操作
首先先看一下这里所说的一次转换成横标的这一步骤，需要借助pivot，一步一步来
然后看跟主表join之后，两种查询方式的整体查询结果
那么看一下后一种查询方式也即通过行业转换之后做join的执行计划，可以看到只对字表进行了一次查找（这里是index seek，但是暂抛开索引）
观察一下两条SQL的IO信息，可以发现，前者的Scan count是5，逻辑读是65，后者的Scan count是1，逻辑读是13，65=13*5。可见后者是一次性将表中的几个Key值读取出来的，而前者每个Key值读取一次表。
总结：
改写SQL是实现优化的思路之一，当然改写SQL技巧有很多种，本文仅对某一类典型查询提供一个改写思路，避免对一个表进行多次读取的方式来实现的查询。
通过改写一个常用的查询写法，从而实现一个等价的逻辑来减少对基表的读取次数来达到SQL优化的目的。
当然实际情况可能更加复杂，采用该思路改写的时候要注意针对SQL语句测试验证。
附上本文的测试脚本
create table HeaderTable( HeaderId int , OtherColumn varchar(50))create table DetailTable( HeaderId int, DetailId int identity(1,1), DetailKey varchar(50), DetailValues int)declare @i int = 0while @i<1000000begin insert into HeaderTable values (@i,NEWID()) insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0001‘,RAND()*10000) insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0002‘,RAND()*10000) insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0003‘,RAND()*10000) insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0004‘,RAND()*10000) insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0005‘,RAND()*10000) insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0006‘,RAND()*10000) insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0007‘,RAND()*10000) insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0008‘,RAND()*10000) insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0009‘,RAND()*10000) insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0010‘,RAND()*10000) set @i=@i+1endcreate index idx_HeaderId on HeaderTable(HeaderId)create index idx_HeaderId on DetailTable(HeaderId)create index idx_DetailKey on DetailTable(DetailKey)select *,(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and t.DetailKey = ‘A0001‘) as ‘Key1的值‘,(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and t.DetailKey = ‘A0002‘) as ‘Key2的值‘,(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and t.DetailKey = ‘A0003‘) as ‘Key3的值‘,(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and t.DetailKey = ‘A0004‘) as ‘Key4的值‘,(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and t.DetailKey = ‘A0005‘) as ‘Key5的值‘from HeaderTable a where a.HeaderId = 10000SELECT a.*,t.A0001 as ‘Key1的值‘,t.A0002 as ‘Key2的值‘,t.A0003 as ‘Key3的值‘,t.A0004 as ‘Key4的值‘,t.A0005 as ‘Key5的值‘from HeaderTable a inner join(select HeaderId ,DetailKey ,DetailValues from DetailTable)t pivot( MAX(DetailValues) FOR DetailKey IN (A0001,A0002,A0003,A0004,A0005))t on t.HeaderId = a.HeaderIdwhere a.HeaderId = 10000
SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句