T-SQL like charindex patindex 性能比较

事实上在网上可以找到很多这方面的资料，在这边就不多说了~主要观点在性能方面还是比较倾向于charindex，下面就测试下：

测试环境：共50批次，每批次50000数据，测试总共250万数据.

一、like搜索如下：

declare @count int
declare @batch int
declare @totalBatch int
declare @patternArr varchar(1000)
declare @pattern varchar(200)
declare @times int
declare @timediff datetime 

set @timediff = GETDATE()
set @times = 0
set @count = 50000
set @batch = 1
set @totalBatch = (select max(PostID) from club_Posts) / @count
set @patternArr = '\[contest\]%,%\[flv\]%'

print '执行开始！'
while @batch < @totalBatch
begin

    declare @next int 
    set @next=1 
    while @next<=dbo.Get_StrArrayLength(@patternArr,',') 
        begin 
        set @pattern = dbo.Get_StrArrayStrOfIndex(@patternArr,',',@next)
            update club_Posts set IsAudit=0 where PostID in
            (
                select PostID from club_Posts with(nolock) where IsActive=1 and (IsAudit=0 or IsAudit is null) and Body like @pattern {escape'\'} and PostID between (@batch-1)*@count and @batch*@count
            )
        set @times = @times + @@ROWCOUNT
        set @next=@next+1
        end
    set @batch = @batch +1
    if @batch > 1 break
end
select '执行完成！'+'成功执行'+cast(@times as varchar(20))+'次' as Success, '用时：' +cast(DATEDIFF(MS, @timediff, GETDATE()) as varchar(30)) +'毫秒' as OverTime 

测试结果：--执行完成！成功执行4453次 用时：47233毫秒

declare @count int
declare @batch int
declare @totalBatch int
declare @patternArr varchar(1000)
declare @pattern varchar(200)
declare @times int
declare @timediff datetime 

set @timediff = GETDATE()
set @times = 0
set @count = 50000
set @batch = 1
set @totalBatch = (select max(PostID) from club_Posts) / @count
--set @patternArr = '\[contest\]%,%\[flv\]%'
set @patternArr = '[contest],[flv]'

print '执行开始！'
while @batch < @totalBatch
begin

    declare @next int 
    set @next=1 
    while @next<=dbo.Get_StrArrayLength(@patternArr,',') 
        begin 
        set @pattern = dbo.Get_StrArrayStrOfIndex(@patternArr,',',@next)
--            update club_Posts set IsAudit=0 where PostID in
--            (
--                select PostID from club_Posts with(nolock) where IsActive=1 and (IsAudit=0 or IsAudit is null) and Body like @pattern {escape'\'} and PostID between (@batch-1)*@count and @batch*@count
                select PostID from club_Posts with(nolock) where IsActive=1 and (IsAudit=0 or IsAudit is null) and charindex(@pattern,Body) > 0 and PostID between (@batch-1)*@count and @batch*@count
--            )
        set @times = @times + @@ROWCOUNT
        set @next=@next+1
        end
    set @batch = @batch +1
    if @batch > 50 break
end
select '执行完成！'+'成功执行'+cast(@times as varchar(20))+'次' as Success, '用时：' +cast(DATEDIFF(MS, @timediff, GETDATE()) as varchar(30)) +'毫秒' as OverTime 

--执行完成！成功执行4511次 用时：67410毫秒  

 

结果还是有点让我吃惊啊，虽然上面有点小差异，而且网上还是有很多观点。

 

下面我再测试50万数据看看：

--执行完成！成功执行3次  　　用时：11203毫秒   用时：10610毫秒　　(like)

   --　　　　　　　　　　　　用时：10470毫秒　　 (patindex)
--执行完成！成功执行24次     用时：15226毫秒  用时：15850毫秒　　(charindex)

 

都差不多，不过patindex和charindex稍微优一点，不过差别好像不在。