sql server参数空值判断 len/isnull/is not null/is null
存储过程中，对传入参数进行空值判断，经测试10次每次100万次isnull(@par,”)=”、@par is null、@par is not null 判断取平均值，结果如下：
测试代码如下：
declare @loop int = 1000000
,@par nvarchar(20) = null
,@startTime datetime = getdate()
,@subTime int=0
,@times int =10
while @times>0
begin
set @loop = 1000000
set @startTime=getdate()
while @loop>0
begin
if isnull(@par,'')=''
set @loop = @loop -1
end
set @times = @times -1
set @subTime = @subTime + datediff(MILLISECOND,@startTime,getdate())
end
select @subTime / 10
set @times=10
set @subTime=0
while @times>0
begin
set @loop = 1000000
set @startTime = getdate()
while @loop>0
begin
if @par is null
set @loop = @loop -1
end
set @times = @times -1
set @subTime = @subTime + datediff(MILLISECOND,@startTime,getdate())
end
select @subTime / 10
set @times=10
set @subTime=0
while @times>0
begin
set @loop = 1000000
set @startTime = getdate()
while @loop>0
begin
if len(@par)>0
set @loop = @loop -1
else
set @loop = @loop -1
end
set @times = @times -1
set @subTime = @subTime + datediff(MILLISECOND,@startTime,getdate())
end
select @subTime / 10
本次测试是对传入参数null值判断效率，不代表sql语句中的判断效率。
在where条件中进行参数空判断，结果如下
测试代码如下：
declare @loop int = 1000
,@par nvarchar(20) = null
,@startTime datetime = getdate()
,@subTime int=0
,@times int =1
while @times>0
begin
set @loop = 100
set @startTime=getdate()
while @loop>0
begin
if (select count(1) from lot where (isnull(@par,'')='' or lotid=@par))>0
set @loop = @loop -1
end
set @times = @times -1
set @subTime = @subTime + datediff(MILLISECOND,@startTime,getdate())
end
select @subTime / 1
set @times=1
set @subTime=0
while @times>0
begin
set @loop = 100
set @startTime = getdate()
while @loop>0
begin
if (select count(1) from lot where @par is null or lotid=@par )>0
set @loop = @loop -1
end
set @times = @times -1
set @subTime = @subTime + datediff(MILLISECOND,@startTime,getdate())
end
select @subTime / 1
set @times=1
set @subTime=0
while @times>0
begin
set @loop = 100
set @startTime = getdate()
while @loop>0
begin
if (select count(1) from lot where len(@par) >0 or lotid=@par )>0
set @loop = @loop -1
else
set @loop = @loop -1
end
set @times = @times -1
set @subTime = @subTime + datediff(MILLISECOND,@startTime,getdate())
end
select @subTime / 1