22,209
社区成员
发帖
与我相关
我的任务
分享
declare @a varchar(10) set @a=null
select count(*) from tb where @a is null -- <100ms
select count(*) from tb where col1 like null-- <2ms
select count(*) from tb where col1 like '%'+@a -- 20s
select count(*) from tb where (@a is not null and col1 like '%'+@a) -- <2ms
select count(*) from tb where (@a is null or col1 like '%'+@a) -- 20s
select count(*) from tb where (@a is not null and col1 like '%'+@a) or @a is null -- 20s
--col1列有索引
select count(*) from tb where status='aa' and col1 like '%a%' --311647条记录 499 ms.
select count(*) from tb where status='bb' and col1 like '%a%' --5058条记录 309 ms.
--没索引
select count(*) from tb where status='aa' and col1 like '%a%' --311647条记录 19431 ms.
select count(*) from tb where status='bb' and col1 like '%a%' --5058条记录 40 ms.
select count(*) from tb where col1 like '%a%'
create index testindex on tb(col1 )
select count(*) from tb where col1 like '%a%'
--执行时间:
SQL Server parse and compile time:
CPU time = 16 ms, elapsed time = 16 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 609 ms, elapsed time = 15232 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 16 ms.
SQL Server Execution Times:
CPU time = 3984 ms, elapsed time = 15507 ms.
SQL Server Execution Times:
CPU time = 3984 ms, elapsed time = 15727 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 242 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
--返回的记录数为319044,有索引跟没索引差距是巨大的。
declare @sql varchar(max)
set @sql = 'select count(*) from tb where (1=1)'
if @a is not null
set @sql = @sql+' and col1 like ''%'+@a+'%'''
exec (@sql)
-----------------------------------------------
select count(*) from tb where [col1] like isnull(@a,[col1])
declare @sql varchar(max)
set @sql = 'select count(*) from tb where (1=1)'
if @a is not null
set @sql = @sql+' and col1 like ''%''+@a+''%'''
exec sp_executesql @sql, '@a varchar(100', @a
if @a=null
begin
end
else
begin
select count(1) from tb where col1 like '%'+@a
end