SQL Server 性能调优之查询从20秒至2秒的处理方法
作者:Emrys5 发布时间:2024-01-24 14:01:56
一、需求
需求很简单,就是需要查询一个报表,只有1个表,数据量大约60万左右,但是中间有些逻辑。
先说明一下服务器配置情况:1核CPU、2GB内存、机械硬盘、Sqlserver 2008 R2、Windows Server2008 R2 SP1和阿里云的服务器,简单说就是阿里云最差的服务器。
1、原始表结构
非常简单的一张表,这次不讨论数据冗余和表字段的设计,如是否可以把Project和Baojian提出成一个表等等,这个是原始表结构,这个目前是没有办法改变的。
2、查询的sql语句为
select *from(
select *,ROW_NUMBER() OVER (ORDER BY sc desc) as rank
from(
select *,
case when ( 40-(a.p*(case when a.p > 0 then 1 else -0.5 end)))<=30
then 30
else ( 40-(a.p*(case when a.p > 0 then 1 else -0.5 end)))
end as sc
from (
select * from (
select a.ProjectNumber, a.ProjectName, a.BaojianNumber, a.BaojianName, a.ToubiaoPerson,
sum(UnitPrice) as sumPrice,
b.price as avgPrice,
((sum(UnitPrice)-b.price)/nullif(b.price,0)*100) as p,
sum(case when UnitPrice>b.price then b.price else UnitPrice end )as pprice,
sum(case when UnitPrice>MaxPrice then 1 else 0 end ) as countChao
from ToubiaoDetailTest1 a
join (
select ProjectNumber, ProjectName, BaojianNumber, BaojianName, avg(price) as price
from(
select * from(
select ProjectNumber, ProjectName, BaojianNumber, BaojianName, ToubiaoPerson,
SUM(UnitPrice) as price,
SUM(case when UnitPrice>MaxPrice then 1 else 0 end ) as countChao
from ToubiaoDetailTest1
group BY ProjectNumber, ProjectName, BaojianNumber, BaojianName, ToubiaoPerson
) tt
where tt.countChao = 0
) t
group by ProjectNumber, ProjectName, BaojianNumber, BaojianName
) b
on a.ProjectNumber=b.ProjectNumber and a.ProjectName=b.ProjectName and a.BaojianNumber=b.BaojianNumber and a.BaojianName=b.BaojianName
group by a.BaojianNumber, a.BaojianName, a.ProjectNumber, a.ProjectName, a.ToubiaoPerson, b.price
) tt
where tt.countChao=0
) a
) b
) t
order by rank
此段sql语句主要的功能是:
1、根据ProjectNumber, ProjectName, BaojianNumber, BaojianName, ToubiaoPerson分组,查询所有数据的sum(UnitPrice)
其中UnitPrice>MaxPrice的判断是为了逻辑,如果有一条数据满足,则此分组所有的数据不查询。
2、根据ProjectNumber, ProjectName, BaojianNumber, BaojianName 分组,查询所有数据的avg(price),以上两步主要就是为了查询根据ProjectNumber, ProjectName, BaojianNumber, BaojianName分组的avg(price)值。
3、然后根据逻辑获取相应的值、分数和按照分数排序分页等等操作。
二、性能调优
在未做任何优化之前,查询一次的时间大约为20秒左右。
1、建立索引
根据sql语句我们可以知道,会根据5个字段(ProjectNumber, ProjectName, BaojianNumber, BaojianName, ToubiaoPerson)进行分组聚合,所以尝试添加非聚集索引idx_calc。
在索引键列添加ProjectNumber, ProjectName, BaojianNumber, BaojianName, ToubiaoPerson。如图:
然后执行查询sql语句,发现执行时间已经减半了,只要10610毫秒。
2、索引包含列
分析查询sql可以得知,我们需要计算的值只有UnitPrice和MaxPrice,所以想到把UnitPrice和MaxPrice添加到idx_calc的包含列中。如图
然后执行查询sql语句,发现执行时间再次减半,只要6313毫秒,现在已经从之前的20多秒优化成6秒多。
3、再次优化查询Sql
再次分析sql语句可以把计算所有数据的avg(price)语句暂时放置临时表(#temp_table)中,再计算其他值的时候直接从临时表中(#temp_table)获取数据。
然后执行查询sql语句,执行时间只有2323毫秒。
在硬件、表数据量和查询稍复杂的情况下,这样已经可以基本上满足查询需求了。
三、总结
经过三步:1、建立索引,2、添加包含列,3、用临时表。用三步可以把查询时间从20秒优化至2秒。
以上所述是小编给大家介绍的SQL Server 性能调优之查询从20秒至2秒网站的支持!
来源:http://www.cnblogs.com/emrys5/archive/2017/07/19/sqlserver_index.html
猜你喜欢
- 一.图像金字塔图像金字塔是指由一组图像且不同分别率的子图集合,它是图像多尺度表达的一种,以多分辨率来解释图像的结构,主要用于图像的分割或压缩
- interfaceGo语言里面设计最精妙的应该算interface,它让面向对象,内容组织实现非常的方便,当你看完这一章,你就会被inter
- 现在做的一个小项目需要用到python的相关知识,但是因为太久没用一些东西都忘掉了,因此在本篇博客中记录一下python的函数和类的基础知识
- asp正则表达式检测字符串是否是数字及字母。<% '函数:CheckString(strng) '
- 大家应该经常看到在文本框里提示文字,然后一点就没了。通常做法都是默认给个value,通过js来处理。详细实现都不介绍了,大家都会。现在来看一
- 不是很难哦,我们现在就可以实现VBScript在服务器端验证,就是下面的函数:<%FUNCTION TestString(S
- MyISAM 是MySQL中默认的存储引擎,一般来说不是有太多人关心这个东西。决定使用什么样的存储引擎是一个很tricky的事情,但是还是值
- 本文实例讲述了ThinkPHP5&5.1实现验证码的生成、使用及点击刷新功能。分享给大家供大家参考,具体如下:验证码现在是用户登录、
- 一封电子邮件的旅程是:MUA:Mail User Agent——邮件用户代理。(即类似Outlook
- 本文实例讲述了Python常见数据结构之栈与队列用法。分享给大家供大家参考,具体如下:Python常见数据结构之-栈首先,栈是一种数据结构。
- 一年一度的元宵节刚刚过去,由于时间关系,在元宵节当天晚上11点多才完成本文灯笼的绘制。这两天又在忙着别的事情,所以现在才跟大家分享。一、效果
- 原来sql还有个stuff的函数,很强悍。 一个列的格式是单引号后面跟着4位的数字,比如'0003,'0120,'4
- 本文实例讲述了php将12小时制转换成24小时制的方法。分享给大家供大家参考。具体如下:php将12小时制转换成24小时制,输入格式为:02
- 对于一个多元函数,用最速下降法(又称梯度下降法)求其极小值的迭代格式为其中为负梯度方向,即最速下降方向,αkαk为搜索步长。一般情况下,最优
- 本文实例为大家分享了python实现画圆功能的具体代码,供大家参考,具体内容如下import numpy as np import matp
- 生成一列sum_age 对age 进行累加df['sum_age'] = df['age'].cumsum(
- 一些MySQL发布对MySQL数据库中的系统表的结构进行了更改,添加了新权限或特性。当你更新到新版本MySQL,你应同时更新系统表,以确保它
- 1.创建应用django-admin startproject cloudmscd cloudmspython manage.py star
- 除了使用Django内置表单,有时往往我们需要自定义表单。对于自定义表单Post方式提交往往会带来由CSRF(跨站请求伪造)产生的错误&qu
- 前言之前看到 RunCat 一只可以在电脑上奔跑猫,其主要的功能是监控电脑的CPU、内存的使用情况,使用越多跑的越快。所以准备做一只在任务栏