专注,勤学,慎思。戒骄戒躁,谦虚谨慎

just do it

导航

SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句

原文出处:http://www.cnblogs.com/wy123/p/5933734.html

 

 

先看常用的一种表结构设计方式:

 

那么可能会遇到一种典型的查询方式,主子表关联,查询子表中的某些(或者全部)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 = 0
while @i<1000000
begin
    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+1
end

create 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 = 10000


SELECT 
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.HeaderId
where a.HeaderId = 10000

 

  

posted on 2016-10-06 13:52  MSSQL123  阅读(3700)  评论(1编辑  收藏  举报