问题场景
在sqlserver上直接执行一个查询和插入的语句只需20来分钟,
在存储过程执行居然要4个多小时,这能忍?
原因
在SQL Server中有一个叫做 “Parameter sniffing”的特性。SQL Server在存储过程执行之前都会制定一个执行计划。
如果SQL在编译的时候并不知道参数的值是多少,那么它在执行执行计划的时候就要会进行大量的猜测。
假设传递给参数大部分都是非空字符串,而FACT表查询的字段中有40%都是null,
那么SQL Server就会选择全表扫描而不是索引扫描来对参数制定执行计划。
全表扫描是在参数为空或为0的时候最好的执行计划。但是全表扫描严重影响了性能。
简单概括就是,要给查询的参数设限制(尤其是允许参数为空的情况),不要让它放飞自我
解决办法
解决的办法有几种,其中一种最简单的办法是进行参数替换
把存储过程内定义一个参数去顶替存储过程的输入参数
CREATE PROCEDURE [dbo].[proc_test]
@var_thedate VARCHAR(30)=''
AS
BEGIN
declare @THEDATE VARCHAR(30)
IF @var_thedate = '' or @var_thedate IS NULL
BEGIN
SET @var_thedate=CONVERT(VARCHAR(30),GETDATE()-1,112);
END
SET @THEDATE=@var_thedate;
select * FROM test WHERE THEDATE=@THEDATE;
END