继续调优,今天上午分析了以下一条处理时间达40秒的SQL语句
select *
from table
where T_table_ID in
(
select distinct s.t_table_id
from
(
select distinct a.t_table_id,a.bt
from
(select left(bt,4) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0) a,
(select distinct left(bt,4) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0) b
where b.bbt like a.bbt and a.t_table_id<>b.t_table_id
and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%'
and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%'
and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%'
and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%'
-- order by a.bt
union all
select distinct a.t_table_id,a.bt
from
(select right(bt,5) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0) a,
(select distinct right(bt,5) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0) b
where b.bbt like a.bbt and a.t_table_id<>b.t_table_id
and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%'
and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%'
and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%'
and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%'
and a.bbt not like '%'+(select right(convert(varchar(10),getdate()-1,20),2)+')') +'%'
and b.bbt not like '%'+(select right(convert(varchar(10),getdate()-1,20),2)+')') +'%'
) s
)order by bt
基本上可以认为是对同一张表的反复操作,而且语句中夹杂了太多的全表扫描
SQLServer的执行计划我个人认为图形化界面固然是好,但是有些时候对于量化的I/O,CPU,COST输出却很不直观,此外像该SQL这样的执行计划,估计1600*1200的整个屏幕都无法显示,可以认为基本是没法看的
只能将SQL分解成若干小SQL,逐步找到瓶颈所在,例如
select left(bt,4) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
select distinct left(bt,4) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
这两个语句的执行都非常快,并且结果集也比较小,但是两条语句合并后并加上相关条件就非常缓慢。
干脆直接构建两个临时表,反正都是全表扫描,用两个临时表做相互的join,测试之后发现只需要1秒
再构建下面的两个SQL临时表,也做同样的测试
最后再全部合并到一起进行测试,发现也就是2~3秒
实际上还可以再优化一些临时表的构建,但效果达到了也就不愿意尝试了
也尝试过用CTE,不过似乎效果不佳
以下为优化后的SQL样例
/*
with temp1 as
(select left(bt,4) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0),
temp2 as
(select distinct left(bt,4) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0),
temp3 as
(select left(bt,5) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0),
temp4 as
(select distinct left(bt,5) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0)
*/
print convert(varchar,getdate(),9)
select left(bt,4) as bbt,* into #temp1 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
select distinct left(bt,4) as bbt,t_table_id into #temp2 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
select right(bt,5) as bbt,* into #temp3 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
select distinct right(bt,5) as bbt,t_table_id into #temp4 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
select
(select ms from xtclb where dm=lmxz and lb in (130,131) ) as '栏目选择',
bt,mtly,czy
from table
where T_table_ID in
(
select distinct s.t_table_id
from
(
select distinct a.t_table_id,a.bt
from
#temp1 a,
#temp2 b
where b.bbt like a.bbt and a.t_table_id<>b.t_table_id
and a.bbt not in ('aaaa','bbbb','cccc','dddd','eeee','ffff')
and b.bbt not in ('aaaa','bbbb','cccc','dddd','eeee','ffff')
union all
select distinct a.t_table_id,a.bt
from
#temp3 a,
#temp4 b
where b.bbt like a.bbt and a.t_table_id<>b.t_table_id
and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%'
and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%'
and a.bbt not like '%'+(select right(convert(varchar(10),getdate()-1,20),2)+')') +'%'
and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%'
and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%'
and b.bbt not like '%'+(select right(convert(varchar(10),getdate()-1,20),2)+')') +'%'
) s
)order by bt
--OPTION (loop join);
--34
print convert(varchar,getdate(),9)
/*
drop table #temp1
drop table #temp2
drop table #temp3
drop table #temp4
*/
分享到:
相关推荐
SQLServer安全及性能优化 修补漏洞 安装程序补丁修补漏洞 随时关注微软官方网站补丁升级 关闭不必要的端口 关闭联必要的服务 数据库引擎 SQL Server Analysis Services SQL Server Reporting Services SQL ...
是Inside Microsoft SQL Server 2005系列书中的第一本,SQL Server类的顶尖之作 全球公认SQL Server 2005经典著作,囊括大量鲜为人知的技术内幕,大师智慧、专家经验尽览无余。 本系列图书中文版得到了微软...
tempdb是SQLServer的系统数据库一直都是SQLServer的重要组成部分,用来存储临时对象。可以简单理解tempdb是SQLServer的速写板。应用程序与数据库都可以使用tempdb作为临时的数据存储区。一个实例的所有用户都共享一...
第21章 性能优化工具 21.1 优化时机(第二部分) 21.2 日常维护 21.3 故障排除 21.4 小结 第22章 管理 22.1 计划作业 22.2 备份和恢复 22.3 索引维护 22.4 数据存档 22.5 PowerShell ...
第21章 性能优化工具 21.1 优化时机(第二部分) 21.2 日常维护 21.3 故障排除 21.4 小结 第22章 管理 22.1 计划作业 22.2 备份和恢复 22.3 索引维护 22.4 数据存档 22.5 PowerShell ...
第1章 RDBMS基础:SQLServer数据库的构成 1.1 数据库对象概述 1.1.1 数据库对象 1.1.2 事务日志 1.1.3 最基本的数据库对象:表 1.1.4 文件组 1.1.5 数据库关系图 1.1.6 视图 1.1.7 存储过程 1.1.8 用户自定义函数 ...
1.数据和日志文件分开存放在不同磁盘上 ...由于建立和填充临时表会严重降低系统性能,所以在尽可能的情况下应该为要排序的列建立索引。同时,tempdb数据库是为所有的用户和应用程序共享,所以如果一个用户占
第1章 RDBMS基础:SQLServer数据库的构成 1.1 数据库对象概述 1.1.1 数据库对象 1.1.2 事务日志 1.1.3 最基本的数据库对象:表 1.1.4 文件组 1.1.5 数据库关系图 1.1.6 视图 1.1.7 存储过程 1.1.8 用户自定义函数 ...
第21章 性能优化工具 21.1 优化时机(第二部分) 21.2 日常维护 21.3 故障排除 21.4 小结 第22章 管理 22.1 计划作业 22.2 备份和恢复 22.3 索引维护 22.4 数据存档 22.5 PowerShell ...
第21章 性能优化工具 21.1 优化时机(第二部分) 21.2 日常维护 21.3 故障排除 21.4 小结 第22章 管理 22.1 计划作业 22.2 备份和恢复 22.3 索引维护 22.4 数据存档 22.5 PowerShell ...
第1章 RDBMS基础:SQLServer数据库的构成 1.1 数据库对象概述 1.1.1 数据库对象 1.1.2 事务日志 1.1.3 最基本的数据库对象:表 1.1.4 文件组 1.1.5 数据库关系图 1.1.6 视图 1.1.7 存储过程 1.1.8 用户自定义函数 ...
第21章 性能优化工具 21.1 优化时机(第二部分) 21.2 日常维护 21.3 故障排除 21.4 小结 第22章 管理 22.1 计划作业 22.2 备份和恢复 22.3 索引维护 22.4 数据存档 22.5 PowerShell ...
第21章 性能优化工具 21.1 优化时机(第二部分) 21.2 日常维护 21.3 故障排除 21.4 小结 第22章 管理 22.1 计划作业 22.2 备份和恢复 22.3 索引维护 22.4 数据存档 22.5 PowerShell ...
该文档描述SQLSERVER内部运行机制,保存页,索引存储,临时表和表变量的选择。常用SQL代码优化技巧,注意网络硬件操作系统及数据库设计对sql优化的限制
存储过程的安全及性能优化 存储过程分类 系统存储过程 自定义存储过程 SQL Server使用者编写的存储过程 扩展存储过程 动态链接库(DLL)函数的调用看,主要用于客户端和服务器端之间进行通信 exec...
一、分析阶段 一般来说,在系统分析阶段往往有太多需要关注的地方,系统各种功能性、可用性、可靠性、安全性需求往往吸引了我们大部分的注意力,但是,我们必须注意,性能是很重要的非功能性需求,必须根据系统的...
数据库在被访问的时候,都是执行SQL语句,在执行之前系统需要确定访问方式及执行的过程,在执行的时候是否使用数据索引,是否使用临时数据表,临时数据表有没有存储,存储在哪个物理介质模块上了。如果使用索引,...
随着用户的日益递增,日活和峰值的暴涨,数据库处理性能面临着巨大的挑战。下面分享下对实际10万+峰值的平台的数据库优化方案。与大家一起讨论,互相学习提高! 案例:游戏平台. 1、解决高并发 当客户端连接数达到...
网上有不少mysql 性能优化方案,不过,mysql的优化同sql server相比,更为麻烦,同样的设置,在不同的环境下 ,由于内存,访问量,读写频率,数据差异等等情况,可能会出现不同的结果,因此简单地根据某个给出方案来...