本文主要介绍写SQL的另外两个误区:
一、关于存储过程使用局部变量,我们举例说明。 有这么一张表 存储过程: 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:我相信那是一个好坑:))。
|
|