ITPub博客

首页 > 数据库 > SQL Server > SQL优化实战

SQL优化实战

原创 SQL Server 作者:gulunhua 时间:2016-03-24 18:32:24 0 删除 编辑


一、结存数据太慢(SQL Server数据库)
一开始,客户经理说客户,不想在多个查询中看数据,想在一个表中看多所有的数据,也就是说需要把原有的多个查询的sql合并为一个,但是实际上合并后,每个月的原始数据量大概是100w条左右,然后进行计算,最后返回大概3w条左右,速度比较慢。

然后,客户反馈,在查询数据时非常慢,基本上查询1个月的数据,需要5分钟,于是对sql中涉及到的表,都更新了统计信息,速度提高不少。

之后,客户说需要显示结存的数据,于是修改sql,增加了表关联,但是速度又慢了。

再之后,尝试用了修改sql,但是效果都不理想。最后想出的办法是,新建结存表,新建存储过程,然后把数据预先结存到表中。

通过一个存储过程,来结存2015年全年的数据,每次结存一个月的数据。
在结存1、2、3月时,每个月在2分钟左右,但从4月份开始,结存一个月数据10分钟都不止。

一开始,由于结存时间过长,也无法判断有多少数据已经结存进去,所以直接kill,但是rollback时间达到了20分钟。

后来,通过 select * from tb with(nolock)查询,大致能掌握已结存进去多少数据。

接下来进行了几种尝试:
1、rebuild index,通过重建索引,来减少索引的碎片。
2、删除日期字段biz_date上的索引,减少需要维护的索引数量,加快insert的速度。
3、删除聚集索引,同2。
4、先插入临时表,再把临时表的数据插入表中。
但是以上4种尝试,基本上无效,通过:
select * from sys.sysprocesses where spid = xx
发现查询数据的时候比较快,但是在insert时很慢,基本上都是PAGEIOLATCHEX等待,这个等待基本上说明是 由于磁盘的写入等待,导致速度很慢。

那么在磁盘写入速度很慢的情况下,如何提高速度呢?
尝试了下面的2种方法:
1、先把1个月的数据存入临时表,创建索引,然后每1w条数据提交1次。
2、把存储过程修改为按日结存。
最后,这2种方法,都能在3分钟以内,结存一个月的数据,且第1个方法最快,只需要2分钟左右,但是第1个方法需要写的代码比较多。

总结一下:随着表的数据越来越多,插入速度越来越慢,通过分批插入数据,然后提交,来提高速度。
这里的关键是分批,每一批的大小,太大的话,虽然提交次数少了,但是对磁盘压力太大,必然会有长时间等待。
如果太小,那么提交次数太多,甚至每次插入数据的时间比每次提交所消耗的时间还小,而数据很多,这样以计算,就会发现,消耗在提交上的时间,可能都查过了 插入数据的时间。



二、考勤sql查询太慢(Oracle数据库)
原始语句比较复杂,这个sql一共92行,涉及到6个表的多次关联,客户在查询1个月的数据,基本上要10分钟。

接下来做了尝试:
1、原始数据本来从实时表取,修改为从结存表取,减少表的关联,但是修改后速度没有变快。

这种情况下很难优化,时间有紧,不可能重新写,因为这样会引入新的问题,还需要进一步测试。
考虑到最后返回的数据只有几万条,但是客户大部分的时候,只是查询一个月中的 某些人的数据,而不会查询1个月的所有数据,
所以采用了如下的优化方法:
通过把查询条件直接加入到最内存的sql中,而不是由系统在sql语句的最外层加上查询条件,虽然查询整个月的数据,并没有加快速度,但是单独查询部分数据,速度提高到了10秒以内。

总结:
这次优化,并没有修改语句,而是考虑到要快,就要用条件去直接过滤数据,产生小的结果集,然后这个结果集继续和外层的表关联,这样速度就能更快。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/653220/viewspace-2063316/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-08-01

  • 博文量
    41
  • 访问量
    30460