总结一下自己在解决这个问题时采用的办法:
一、
一开始,觉得速度这么慢是不是因为没建立索引而导致的。于是看了一下数据库中相关表的索引信息,发现只建立了聚集索引,其他字段,特别是一些重要的统计字段,表连接时用的字段,都没有建立索引,于是就针对sql语句中要用到的字段,一一建立了索引。然后再运行一下发现,原来要好几分钟才能出结果的sql语句,现在也就几秒钟,这充分显示了索引的巨大威力。
当然,索引也不是万能的,有的时候建立了索引,也解决不了问题。事情是这样的,由于报表的逻辑,加上想要减小开发的复杂性,所以在最后生成结果的时候,把一个表与10个表进行了关联,而这10个表,每个表又是由另外2个表关联产生的,一运行,等了好长时间也没结果,于是想是不是因为数据比较多,同时又没有索引导致这么慢呢?查看相关表的索引信息,发现确实没有建立索引。建好索引后想着这次速度肯定快,可是一运行,还是非常慢,至少得等个半小时才能看到结果。这是怎么回事呢?我想从执行计划中找到点蛛丝马迹,可是这个执行计划太过于庞大,整个执行计划100多行,密密麻麻的一堆,看着头晕。
这时我想可能是由于关联了10个表导致了性能问题,于是我改变了实现方法:把后10个表的结果一次都求出来,然后转置成20列,最后就只有2个表进行关联。在运行之前我把之前建立的索引都删除了,运行一下,这次至少有结果了,3分多钟吧,但这个是在建立了索引之后的结果,于是我把刚才建立的索引都删除了,发现速度基本上没变化。这是怎么回事呢?
我看了查看了主表,里面数据并没有想象的多,这说明在数据不多的情况之下,建立索引产生的效果很小。虽然3分钟已经快很多了,但还不快,在数据不多的情况之下还要3分钟,这到底是怎么啦!
于是开始在sql语句中定位哪一部分是最慢的,一段一段运行,最后发现有一个段是2个表进行left join关联,特别慢,2个表的数据都不多,怎么关联时这么慢呢?
这个时候我把两个表的数据分别插入到两个临时表中,再用临时表进行关联,发现几秒钟就返回了,查看一下执行计划,发现慢的时候,关联用了nested loop算法,而快的时候用了hash 算法,于是把原来的left join改为left hash join,这下速度一下次超快,2秒就出来了。
查了一下资料,说是nested loop算法对于结果集的大小是很敏感,由于在sql语句中这2个关联的表本身也是其它多个表进行了多次关联之后产生的,SQL Server要比较准确的预测这2个表的结果集的大小是比较困难的,预测的失误导致SQL Server认为采用nested loop算法会获得较好的性能,但实际情况恰恰相反,速度极慢,这个时候就需要开发人员通过hint来改变关联算法,从而进行相应的调整。
二、
还有一个问题是我同事碰到的,他开发的一个报表速度比较慢,因为数据量太大了,他一方面改变了实现逻辑的办法,同时我也采用hint(本地连接提示)的方法,这样调整后运行3分多钟能出来结果。我同事提出来能不能再快一点呢?
于是又开始优化,这次就着重于建立索引,这里特别要说的是,SQL Server给予的缺失索引建议,非常有用,它会告诉你应该建立什么索引,按照它的建议建了索引,发现运行1分多钟能出结果,快了2分钟。
三、
最近有一个语句在输入查询条件为2012-02-01时,查询速度15s左右,但是输入条件改为2012-03-01时,查询速度2分钟左右,一开始认为是否是因为第二个语句没有重新编译,它重用了第一个查询的执行计划,所以慢,但是仔细想了想,第二个语句应该是一开始就自动会重新编译,因为服务器参数没有强制参数化,而是简单参数化,像这种有点复杂的语句,应该是会自动重新编译的,那么只能说是第一个查询语句生成的执行计划是比较合适的,第二个语句生成的执行计划肯定与第一个语句的不同,而且这第二个执行计划比较差,那么接下来我把这个查询条件,用一个变量来取代,同时通过 option的optimize for ( 变量=2012-02-01),同时变量值=2012-03-01时,果然速度变化了,也就是传入参数2012-03-01,但是用的执行计划是针对2012-02-01来优化的执行计划。
最后总结一下:
1、要想sql运行效率高,首先还是得用高效的实现方式,同样的要求,可以用多种Sql语句来实现,其中肯定有相对比较高效率的实现方式,上面说的转置就是一种好办法;
2、就是在性能有问题时,可以给SQL Server一些建议,按你的指示来调整优化。虽然SQL Server有很强大的优化器,自动的进行优化,不过既然她提供了hint(本地连接提示),也就是想在优化结果不理想时,为开发人员修正SQL Server优化结果提供了一种手段。
3、在建立索引时,SQL Server给予的建议,也是很值得尝试的。