写有效率的SQL查询（IV）

本文主要介绍写SQL的另外两个误区：
1、 存储过程中使用局部变量而不使用参数变量（就是存储过程输入参数）做where条件
2、 查询条件中类型不匹配

这两种错误都是非常非常容易犯且非常发指的错误，特别是2，太多次见过了。

 

 一、关于存储过程使用局部变量，我们举例说明。

有这么一张表

存储过程：

create proc test

(

    @id int

)

as

select * from charge where charge_no > @id

 

那么exec test 99998，执行计划为：

请注意上图中的估计行数。

 

但是如果把存储过程修改为：

alter proc test

(

    @id int

)

as

declare @local int

set @local = @id

select * from charge where charge_no > @local

 

再次观察exec test 99998的查询计划：

请再次注意估计行数，现在是30000了。而我们都知道，修改前存储过程和修改后的输出结果集都没有任何变化，为2。

由于charge_no是聚集索引，而我们的查询条件是where charge_no > XXX，不论SQLServer估计行数有多大，伊都会使用相同的clustered index seek查找到XXX，然后直接顺序遍历基础表剩下的叶节点。

但是，若charge_no是非聚集索引，由于估计结果集行数大小由两行变成了总行数的百分之三十（使用局部变量做查询条件，这种where AAA > BBB，SQLServer无法估计结果集大小，所以它使用默认估计值：30%），nonclustered index seek变成nonclustered index scan(SQL2k5中若不是覆盖查询，会是clustered index scan)，这是巨大的性能损耗，必须避免。

 

在这里顺带着再次强调另外一个问题：缓存的查询计划可能会强力的伤害性能。为了更详细的说明它，我们把存储过程test改为：

alter proc test

(

    @id int

)

as

select * from charge where charge_no > @id

 

然后看看执行计划exec test 99998(见上面的图，不重复贴了)。再来看看exec test 1的执行计划：

我们可以注意到，尽管真实的结果集变动非常巨大，但是查询计划还是完全不变，SQLServer在使用缓存。这种情况在使用聚集索引时不会让查询变得更糟，但是使用非聚集索引就会差上十万八千里，IO开销会差上n个数量级（n取决于真实的结果集）。

所以如果你的查询由于输入参数的不同，选择性变动剧烈，最好在创建存储过程的时候使用 WITH RECOMPILE 选项。即：

create proc test

(

    @id int

)

with recompile

as

select * from charge where charge_no > @id

 

OK，但并不是所有的情况下在查询条件中使用局部变量都有问题。如果查询条件中涉及的索引，SQLServer发现伊的分布密度非常小（比如一个identity(1,1)列或者一个unique），那么在where AAA = XXX的情况下，SQLServer仍然会认为结果集相对总行数很小，而选择index seek类的查询计划。

 

二、  查询条件中的类型不匹配。

    所谓的类型不匹配是说，查询条件where AAA = @var，列AAA的定义和@var不同。例如，AAA是varchar(64)，@var是bigint。这种情况下，非常有可能让本来是index seek的运算变成index scan，在大数据量表中，性能差距会非常明显。

    从我的经验来看，并不是所有的隐式转换都会带来这样的问题。但是这样的问题大量的存在，并且在分析性能瓶颈、做索引调优时，会给你带来极大的困扰。必须分析缓存中查询计划对应的原始语句，看那玩意属于慢性自杀。

我们写SQL一般都会类型匹配，但是通过应用程序就非常容易出错。比方说一个表有个MobileNo字段用来存储手机号码，表中是varchar。但是应用程序你这么写：

SqlConnection conn = ...;

           SqlCommand cmd = new SqlCommand("select * from Users where MobileNo = @mo", conn);

           cmd.Parameters.Add(new SqlParameter("@mo", 13511223344));

           SqlDataReader reader = cmd.ExecuteReader();

            //....

那么你挂了……

 

    到现在为止，我没有看到任何资料说哪种形式的隐式转换会让SQL无法判定结果集大小或者可以不去爬整棵索引树。所以我的建议是，使用最强类型去匹配查询列。查询列是啥，就写啥。是varchar(64)就别简单的new SqlParamerer(“@mo”, “13511223344”)，要精确指定它的类型、长度。这样做有另外一个好处，偶将在下一篇blog——比较拼SQL、参数化SQL、使用存储过程执行DB指令的优劣时说明(btw:我相信那是一个好坑:))。

==加个总结=====================================
1、存储过程中，能不使用本地变量就不使用，尽可能的使用参数变量（也就是输入参数）。如果不得不使用本地变量，那也得只用在分布密度足够小的索引上使用。
2、写查询条件时，应该尽可能的使类型匹配。使用诸如SqlCommand执行DB指令时，一定要让输入参数从类型到长度严格匹配相应的列。尽管DB端不是所有的隐式转换都会引起性能损耗。
================================================


       稍微提一句，在msdn中SQL Server Database Engine>Troubleshooting the Database Engine > Troubleshooting Queries下有一篇《Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation》，尽管说的粗糙无比外带模棱两可，但还是推荐一读。