标签归档:sql优化

mssql sqlserver 中亿万条数据表如何做到秒查


摘要:
下文讲述优化公司的报表,使用报表从1个小时的运行效率提升到半秒不到就显示出数据的方法分享(非powerBI),如下所示:
实验环境:sql server 2008 R2


该系统是我集团公司的BOSS基础业务管理系统,已运行15年之久,期间经过多次的版本迭代,系统累计了大量的数据。系统数据库中,主营业务记录表的数据都达到了上亿条,里面缓存表都达到了百万级,数据报表的压力可想而知。。。
由于长期无人监管,大家都抱着和稀泥的方式去做事,所以即使无法查询出数据,也无人反馈,可以说公司的IT已经不做事了(因为大家都好多年没涨薪水了。。。)公司的各项信息系统都处于无人监管的状态。最近几年市场竞争激烈,老板才想起来,要搞一下管理,所以才想到了IT,才想到了管理,当然也想到了我们的系统该修一修了。
下文将讲述,我们第一步对数据报表的修复

实践思路:

1.把索引都不补上
索引都补上之后,发现情况还是没有好转,数据的检索速度没有任何改善,
2.对表分区
当我对表 按照日期进行分区后,所有的检索都没有任何改善
3.对sql语句进行分析:
我们发现where条件中采用很多如下的写法:

where 条件中有 like ‘%’+变量+’%’
where 条件中有 case end函数
表连接中存在多次连接同一表的操作
sql返回的数据中存在*

发现了sql脚本中的问题时,此时我们需要做的工作是对sql脚本进行改写:

1.剔除 where 条件中case end函数
对查询条件所对应的表建立临时表,然后将临时表的数据进行汇总
使用临时表可以减少表扫描
2.将in修改为exists ,not in变更为 not exists关键字
3.同使用部门确认,将 where 条件中有 like ‘%’+变量+’%’ 变更为 where 条件中有 like ‘%’+变量+”的可行性
4.在对表的读取时,加入 with(nolock)

通过以上的改写后,sql脚本的运行时间从1小时变更为半秒中。

通过反复的测试和总结,这次效率提升点在于使用了临时表,减少物理表的扫描次数,提升了系统性能

mssql sqlserver 2008 sql查询脚本导致CPU升至100%的解决方法分享


摘要:
下文讲述一次sql脚本导致CPU飘高的处理方法,如下所示:
实验环境:sql server 2008 R2


今天有同事对四张千万级大表进行join查询,导致CPU升至100%,影响系统的正常使用,
下文讲述通过将sql查询设置为并行操作后,CPU可正常工作,设置方法如下所示:

   sp_configure 'show advanced options',1 reconfigure with override

   sp_configure 'max degree of parallelism',4 reconfigure with override

mssql sqlserver 影响sql脚本性能的因素


摘要:
下文将讲述影响sql脚本性能的相关因素,如下所示:



索引应用

1.sql检索脚本上where过滤列是否加入合理索引
2.数据表突然增大,导致索引计数不准确,查询计划走错索引
3.过滤列上加入函数,导致过滤条件未走索引 where dbo.fun(test) >88



循环处理处理

循环处理是否涉及大循环(大遍历)
循环尽量采用不锁表的方案(结合实际业务),避免循环的时候出现锁表,导致死循环



临时表与表变量

使用临时表或表变量时,先预估表大小。
在sqlserver中 表变量不受事务控制,写入数据的效率比临时表高;
表变量不会产生计数信息,sqlserver无法预估查询开销,
如果临时表参与数据查询时,我们建议使用临时表作为数据缓存(因为查询分析器可以自动选择最优方案)



其它注意事项

尽量使用短sql
尽量使用短事务
返回可用数据,尽量不返回无需使用的数据占用服务器带宽和网络压力
select 查询中少用 “不等于” 或”not” 这种关键字
sql脚本中不做无用的排序操作
不向客户端返回过多无用的数据
使用联接代替exists(和in)
减少or条件的写法,多使用union all