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

简介: 原文:SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句原文出处:http://www.cnblogs.com/wy123/p/5933734.html     先看常用的一种表结构设计方式:   那么可能会遇到一种典型的查询方式,主子表关联,查询子表中的某些(或者全部)Key点对应的Value,横向显示(也即以行的方式显示)   这种查询方式很明显的一个却显示多次对字表查询(暂时抛开索引)   相比这种查询方式很多人都遇到过,如果子表是配置信息之类的小表的话,问题不大,如果字表数据量较大,可能就会有影响了。
原文: 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

 

  

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
24天前
|
SQL 缓存 关系型数据库
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
40 0
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据查询
【数据库SQL server】关系数据库标准语言SQL之数据查询
57 0
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之视图
【数据库SQL server】关系数据库标准语言SQL之视图
54 0
|
8天前
|
SQL 关系型数据库 MySQL
Flink SQL 问题之查询时报错如何解决
Flink SQL报错通常指在使用Apache Flink的SQL接口执行数据处理任务时遇到的问题;本合集将收集常见的Flink SQL报错情况及其解决方法,帮助用户迅速恢复数据处理流程。
33 4
|
8天前
|
SQL DataWorks 关系型数据库
dataworks数据集问题之sql查询报错如何解决
DataWorks数据集是指在阿里云DataWorks平台内创建、管理的数据集合;本合集将介绍DataWorks数据集的创建和使用方法,以及常见的配置问题和解决方法。
21 1
|
9天前
|
SQL 存储 数据安全/隐私保护
|
11天前
|
SQL 存储
SQL Server基本函数
SQL Server基本函数
|
28天前
|
SQL 关系型数据库 MySQL
mysql 中sql 语句查询今天、昨天、近7天、近30天、一个月内、上一月数据
mysql 中sql 语句查询今天、昨天、近7天、近30天、一个月内、上一月数据
|
28天前
|
SQL BI
sql中从不同表查询并组合数据示例
sql中从不同表查询并组合数据示例
|
1月前
|
SQL 存储 数据库
【数据库SQL server】自学终极笔记
【数据库SQL server】自学终极笔记
76 0

热门文章

最新文章