SQL优化

总结一下自己在解决这个问题时采用的办法：

 

一、

一开始，觉得速度这么慢是不是因为没建立索引而导致的。于是看了一下数据库中相关表的索引信息，发现只建立了聚集索引，其他字段，特别是一些重要的统计字段，表连接时用的字段，都没有建立索引，于是就针对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给予的建议，也是很值得尝试的。