SQL Server性能调教的小实验(2)

上次说到当数据量提高之后，查询效率急剧下降，经过分析后，得到这个查询语句的效率是最低的。

SELECT ID

FROM Specimen_admin_specimen_T

WHERE (Species_ID IN

          (SELECT DISTINCT (Species_ID)

         FROM View_All_Tree

         WHERE genus_ID = '{F689E231-0DF5<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />-40C1-A11D-0B4DD3B8187A}')) OR

      (SubSpecies_ID IN

          (SELECT DISTINCT (SubSpecies_ID)

         FROM View_All_Tree

         WHERE genus_ID = '{F689E231-0DF5-40C1-A11D-0B4DD3B8187A}'))
这个查询返回的结果是684个，Specimen_admin_specimen_T表中有1,019,765条记录。
SET STATISTICS IO ON后，得到的统计数据是：

表 'Table_species'。扫描计数 6248，逻辑读 13772 次，物理读 0 次，预读 0 次。

表 'Table_genus'。扫描计数 14，逻辑读 28 次，物理读 0 次，预读 0 次。

表 'Table_subspecies'。扫描计数 14，逻辑读 156 次，物理读 0 次，预读 0 次。

表 'Specimen_admin_specimen_T'。扫描计数 1，逻辑读 84381 次，物理读 0 次，预读 0 次。

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 而执行计划为
可以看到主要的几个限速步骤是对species标的hash match(species表被变态的扫描了6248次)和对specimen表的全表扫描与排序。看来问题就出在这里。上面的SQL 语句执行了32'。要针对这两个地方进行优化了。
首先要优化视图的效率。

select * from VIEW_All_Species_Without_SubStru where phylum_chinese_name='脊索动物门'
这个视图的执行计划如下：

每执行一次就要进行一次hash match，而且没有一次是利用了索引，全部都是全表扫描。查了一些资料，是因为视图的代码中用到了left outer join，而outer join是无论如何都要全表扫描的。所以，要想办法改掉这个问题。经过分析后，发现设计中完全没有必要用Outer join，而用inner join就可以（当初用outer join完全是为了照顾另一个程序的需要，现在重新建一个视图给那个程序用就可以了）了，于是用inner join改写了这个视图，并用WITH SCHEMABINDING 将这个视图设计为索引视图。并在相应的字段均做上索引。
经过这样的修改后，果然效率有很大的提高。下面是同样的SQL语句的执行结果。
表 'Table_species'。扫描计数 2896，逻辑读 18761 次，物理读 0 次，预读 0 次。
表 'Table_genus'。扫描计数 605，逻辑读 7253 次，物理读 0 次，预读 0 次。
表 'Table_family'。扫描计数 115，逻辑读 2156 次，物理读 0 次，预读 0 次。
表 'Table_order'。扫描计数 6，逻辑读 236 次，物理读 0 次，预读 0 次。
表 'Table_class'。扫描计数 1，逻辑读 13 次，物理读 0 次，预读 0 次。
表 'Table_phylum'。扫描计数 1，逻辑读 3 次，物理读 0 次，预读 0 次。
执行计划：
已经完全没有和hash match。
这样上面的最复杂的SQL语句的执行效率已经提高到了9秒。已经完全能够适应需要了。

转载于:https://www.cnblogs.com/Seraph/archive/2005/03/11/117155.html