Declare @table table( td nvarchar(max))
insert into @table
select 'ABQ,ABR,ABY,ACK,ACT,ACV,ACY,ADQ,AEX,AGS,AHN,AIY,AKN,ALB,ALM,ALO,ALW,AMA,ANC,ANI,AOO,APF,APN,ASE,ATL,ATW,ATY,AUG,AUS,AUW,AVL,AVP,AZO,BDL,BED,BET,BFI'
union all
select 'ABI,ACT,AEX,ATW,AUW,AZO,BMI,BPT,BRO,BTR,CID,CLL,CMI,CRP,DSM,ELP,EVV,FSM,FWA,GGG,GRB,GRR,HLN,HOU,HRL,ICT,ILE,JAN,LBB,LCH,LFT,LIT,LNK,LSE,MAF,MFE,MKG,MLI,PIA,SBN,SGF,SHV,SPS,TYR'select * from @table t where PATINDEX('%'+ 'ACT' +'%',t.TD) >0
select * from @table t where CHARINDEX('ACT',t.TD) >0
因为我在存储用的这个PATINDEX这个比较频繁,想用CHARINDEX来代替,我怎么测试以后感觉效率PATINDEX这个高啊!!请高手指点!!!谢谢!!
insert into @table
select 'ABQ,ABR,ABY,ACK,ACT,ACV,ACY,ADQ,AEX,AGS,AHN,AIY,AKN,ALB,ALM,ALO,ALW,AMA,ANC,ANI,AOO,APF,APN,ASE,ATL,ATW,ATY,AUG,AUS,AUW,AVL,AVP,AZO,BDL,BED,BET,BFI'
union all
select 'ABI,ACT,AEX,ATW,AUW,AZO,BMI,BPT,BRO,BTR,CID,CLL,CMI,CRP,DSM,ELP,EVV,FSM,FWA,GGG,GRB,GRR,HLN,HOU,HRL,ICT,ILE,JAN,LBB,LCH,LFT,LIT,LNK,LSE,MAF,MFE,MKG,MLI,PIA,SBN,SGF,SHV,SPS,TYR'select * from @table t where PATINDEX('%'+ 'ACT' +'%',t.TD) >0
select * from @table t where CHARINDEX('ACT',t.TD) >0
因为我在存储用的这个PATINDEX这个比较频繁,想用CHARINDEX来代替,我怎么测试以后感觉效率PATINDEX这个高啊!!请高手指点!!!谢谢!!
效率方面可能差不多,都用不到索引了
我目前是where,其实在是关联其他的表用join 写在on条件中的。谢谢!
动态与不动态是一样的写法,
效率方面刚才已经说了,就算你有索引,也用不到
(
SELECT * FROM #ONLINE O
WHERE O.departure IS NULL
AND O.feedergateway is not null
AND O.InterlineGateway is not null
AND O.destination is NOT NULL
)R
INNER JOIN @RtnCityCode Rtn ON PATINDEX('%'+ Rtn.cityCode +'%',R.Destination) >0
谢谢您,其实用CHARINDEX可以不用那么复杂啊,直接
select * from @table t where CHARINDEX(@,t.TD) >0
就行啊
漏写了,应该是
select * from @table t where CHARINDEX(@c,t.TD) >0 此外,因为PATINDEX还有一个+'%'通配符的连接啊,所以我想CHARINDEX效率会高点。
如果你只是三个三个分开的还差不多,如果用CIDDD,你查CID是会查出来的,加','后不会查出来
-----------
100000(1 row(s) affected)1)PATINDEX模式DBCC DROPCLEANBUFFERS
GO
1.1)查询
select * from for_CSDN t where PATINDEX('%'+ 'AC' +'%',t.TD) >0 1.2)耗时
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms. (86041 row(s) affected) SQL Server Execution Times:
CPU time = 24282 ms, elapsed time = 350343 ms.
2)清除缓存--Removes all clean buffers from the buffer pool.DBCC DROPCLEANBUFFERS
GOSET STATISTICS TIME ON --Displays the number of milliseconds required to parse, compile, and execute each statement.
GO3)CHARINDEX模式DBCC DROPCLEANBUFFERS
GO3.1)查询
select * from for_CSDN t where CHARINDEX('AC',t.TD) >03.2)耗时
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms. (86041 row(s) affected) SQL Server Execution Times:
CPU time = 23344 ms, elapsed time = 311975 ms.
select * from @table t where CHARINDEX('ACT',t.TD) >0
就这两个语句来说应该一样高吧。它们的主要区别不在于效率而是功能不同
当实现上面这个简单有相同的功能的时候,我感觉优化器会选择相同的优化方案
所以效率是一样的,微软不会搞出来俩函数实现同一个功能一个效率低而另一
个高的事情吧?