开发人员经常需要在数据库表中查询以某种文本模式开始和/或结束的记录。例如，查找名字以“DAV”开头的所有员工。
根据我的经验，SQL 开发人员通常依赖 4 个常用函数来实现这一点。
就在那时，这位好奇的顾问决定将它们正面交锋：LIKE vs SUBSTRING vs LEFT / RIGHT vs CHARINDEX，看看哪个最快。
赛前秀
出于测试目的，使用 Microsoft SQL Server 2014 在具有 128GB 内存、16 核 CPU、额定频率为 2.54 Ghz 的 Windows 2012 Server 上执行 SQL 代码。
为了让事情变得更有趣，测试分为两部分：
- 将测试每个对表的速度
- 将根据常规“字符串”数据测试每个的速度
这里的假设是不会有竞争条件或对此 SQL 代码的多线程调用。这只是一个直接的、正面的测试。
为确保 SQL Server 不会缓存任何查询（或与此相关的任何内容），在每次测试之前运行以下代码：
|
1
2
3
4
5
6
7
8
9
10
|
checkpoint
go
DBCC DROPCLEANBUFFERS
go
DBCC FREESESSIONCACHE
go
DBCC FREEPROCCACHE
go
DBCC FREESYSTEMCACHE('ALL')
go
|
创建并填充了两个表。主键相同。每个表中的三列将包含完全相同的数据，但是：
- 一个将有一个聚集索引
- 另一个将有一个非聚集索引
- 第三个没有索引
只是看看它们是否对性能有任何影响。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
--create temp table to hold all generated data
IF OBJECT_ID('tempdb..#table1')IS NOT NULL
DROP TABLE #table1
Create Table #table1
(
id int identity(1,1),
aGuid uniqueidentifier not null,
aClusteredIndexedColumn varchar(900) not null,
aNonClusteredIndexedColumn varchar(900) not null,
thecount int not null,
aNonIndexedColumn varchar(900) not null
)
--create temp table to hold test results
IF OBJECT_ID('tempdb..#table2') IS NOT NULL
DROP TABLE #table2
Create Table #table2
(
id int not null,
matchedData varchar(900) not null
)
--create the indexes
print CAST(GETDATE() as varchar) + ' Started creating temptable indexes.'
CREATE CLUSTERED INDEX IDX_cl ON #table1 (aClusteredIndexedColumn)
CREATE NONCLUSTERED INDEX IDX_noncl ON #table1(aNonClusteredIndexedColumn)
CREATE NONCLUSTERED INDEX IDX_id ON #table1(id)
print CAST(GETDATE() as varchar) + ' Finished creating temptable indexes.'
|
使用每个函数完成查询以搜索插入到唯一标识符(guid) 中的特定字符串。结果被插入到辅助表中，以确保每个测试的结果数量相同。
完整的源代码可以在本文末尾找到。
您是博彩个人吗？
这是我们的LIKE vs SUBSTRING vs LEFT / RIGHT vs CHARINDEX速度测试的结果，以毫秒为单位。
获胜者以绿色突出显示。在这个速度测试中没有第二名的分数。
| 功能 | 对聚集索引列执行的时间，以毫秒为单位，超过 3 次运行： | |||
| ＃ 记录： | 50,000 | 500,000 | 5,000,000 | 50,000,000 |
| 1：喜欢 | 46, 43, 40 | 406, 413, 403 | 4016, 3986, 3996 | 39940, 39756, 40423 |
| 2：子串 | 46, 46, 46 | 440, 443, 443 | 2513, 2513, 2603 | 24760, 24873, 24270 |
| 40, 43, 43 | 406, 406, 410 | 2523, 2526, 2516 | 24713, 24770, 24823 | |
| 10, 10, 10 | 56, 56, 56 | 590, 593, 576 | 5713, 5683, 5730 | |
| 功能 | 对非聚集索引列执行的时间，以毫秒为单位，超过 3 次运行： | |||
| ＃ 记录： | 50,000 | 500,000 | 5,000,000 | 50,000,000 |
| 1：喜欢 | 43, 40, 40 | 70, 63, 73 | 680, 666, 670 | 7203, 6756, 6716 |
| 2：子串 | 103, 100, 103 | 256, 256, 260 | 2750, 2750, 2763 | 27076, 26940, 27053 |
| 100, 86, 100 | 253, 246, 250 | 2730, 2733, 2730 | 27166, 26633, 27123 | |
| 10, 10, 10 | 56, 53, 60 | 590, 590, 586 | 5810, 5763, 5690 | |
| 功能 | 对非索引列执行的时间，以毫秒为单位，超过 3 次运行： | |||
| ＃ 记录： | 50,000 | 500,000 | 5,000,000 | 50,000,000 |
| 1：喜欢 | 43, 43, 40 | 63, 66, 70 | 670, 676, 663 | 6790, 7153, 6726 |
| 2：子串 | 126, 123, 123 | 396, 396, 396 | 3016, 2810, 2933 | 25463, 25643, 25420 |
| 46, 46, 43 | 246, 250, 253 | 2800, 2496, 2546 | 24690, 24750, 24810 | |
| 10, 6, 6 | 56, 56, 60 | 576, 590, 573 | 5790, 5836, 6276 | |
| 功能 | 对 varchar 字符串执行的时间，以毫秒为单位，超过 3 次运行： | |||
| ＃ 记录： | 50,000 | 500,000 | 5,000,000 | 50,000,000 |
| 1：喜欢 | 126, 87, 75 | 909, 882, 822 | 8358, 8607, 8667 | 88610, 87349, 85341 |
| 2：子串 | 60, 45, 69 | 585, 582, 576 | 5571, 5673, 5670 | 57849, 54552, 56344 |
| 45, 45, 24 | 474, 396, 351 | 3945, 4044, 3990 | 39969, 39135, 40919 | |
| 60, 36, 66 | 618, 636, 564 | 5766, 5937, 5904 | 59773, 58412, 60198 | |
看看谁拥有它！
在查询表列以查找值时，CHARINDEX 显然是无可争议的王者。其他 3 个中的 2 个甚至没有接近我承认他们是“竞争对手”的速度。我预计 LIKE 会做得更好，尤其是在索引列上，但对 CHARINDEX 的统治感到非常惊讶。
在搜索 varchar/string 变量时，LEFT/RIGHT 位居榜首。
简而言之，当您需要在数据的开头或结尾搜索子字符串时：
- 对表列执行查询时，使用 CHARINDEX
- 在 @varchar 字符串变量中搜索时，使用 LEFT/RIGHT
我在下面为您留下了 SQL 代码，因此请随意将其用作执行您自己的性能基准测试的基础。
如果您有任何建议或其他方式，请在下面发表评论并分享知识！
SQL 源代码
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
|
checkpoint
go
DBCC DROPCLEANBUFFERS
go
DBCC FREESESSIONCACHE
go
DBCC FREEPROCCACHE
go
DBCC FREESYSTEMCACHE('ALL')
go
DECLARE @MAX int = 500000
DECLARE @Counter int = 1
DECLARE @WhatToLookFor varchar(10) = '$%XX#abcde'
DECLARE @tempString varchar(900)
DECLARE @tempGUID UniqueIdentifier
DECLARE @Start_Time datetime
DECLARE @End_Time datetime
--create temp table to hold all generated data
IF OBJECT_ID('tempdb..#table1')IS NOT NULL
DROP TABLE #table1
Create Table #table1
(
id int identity(1,1),
aGuid uniqueidentifier not null,
aClusteredIndexedColumn varchar(900) not null,
aNonClusteredIndexedColumn varchar(900) not null,
thecount int not null,
aNonIndexedColumn varchar(900) not null
)
--create temp table to hold test results
IF OBJECT_ID('tempdb..#table2') IS NOT NULL
DROP TABLE #table2
Create Table #table2
(
id int not null,
matchedData varchar(900) not null
)
--create temp table to hold resulting stats
IF OBJECT_ID('tempdb..#stats') IS NOT NULL
DROP TABLE #stats
Create Table #stats
(
id int identity(1,1),
[task] varchar(900) not null,
TotalRecordsCompared int null,
MatchingRecords int null,
StartTime datetime null,
EndTime datetime null,
TimeToRun varchar(50) not null
)
print CAST(GETDATE() as varchar) + ' Started while loop creating temptable data.'
SET NOCOUNT ON
While @Counter <= @MAX
BEGIN
SET @tempGUID = NEWID()
SET @tempString = CAST(@tempGuid as varchar(2000))
--To ensure some matches
If (@Counter % 9 = 0)
--Put at beginning
SET @tempString = @WhatToLookFor + @tempString
Else if (@Counter % 3 ) = 0
--put at end
SET @tempString = @tempString + @WhatToLookFor
--populate the temp table
INSERT INTO #table1
VALUES
(
@tempGuid
,@tempString
,@tempString
,@Counter
,@tempString
)
SET @Counter = @Counter + 1
END
SET NOCOUNT OFF
print CAST(GETDATE() as varchar) + ' Finished creating temptable data.'
--create the indexes
print CAST(GETDATE() as varchar) + ' Started creating temptable indexes.'
CREATE CLUSTERED INDEX IDX_cl ON #table1(aClusteredIndexedColumn)
CREATE NONCLUSTERED INDEX IDX_noncl ON #table1(aNonClusteredIndexedColumn)
CREATE NONCLUSTERED INDEX IDX_id ON #table1(id)
print CAST(GETDATE() as varchar) + ' Finished creating temptable indexes.'
--verify creation
--select top 10 * from #table1 order by id
--run the tests!
--against clustered indexed column first -------------------------------------------
print 'Starting LIKE test against clustered indexed column'
SET @START_TIME = GETDATE()
INSERT INTO #table2
SELECT id, aClusteredIndexedColumn
FROM #table1
WHERE aClusteredIndexedColumn like @WhatToLookFor + '%'
or
aClusteredIndexedColumn like '%' + @WhatToLookFor
SET @END_TIME = GETDATE()
print 'Finished'
INSERT INTO #stats
SELECT 'LIKE test against clustered indexed column'
, @MAX
, count(*) as [Matching Records]
, @START_TIME as [Start Time]
, @END_TIME as [End Time]
, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #table2
Truncate Table #table2
print 'Starting SUBSTRING test against clustered indexed column'
SET @START_TIME = GETDATE()
INSERT INTO #table2
SELECT id , aClusteredIndexedColumn
FROM #table1
WHERE SUBSTRING(aClusteredIndexedColumn,1, LEN(@WhatToLookFor)) = @WhatToLookFor
or
SUBSTRING(aClusteredIndexedColumn, LEN(aClusteredIndexedColumn) - LEN(@WhatToLookFor) + 1, LEN(@WhatToLookFor)) = @WhatToLookFor
SET @END_TIME = GETDATE()
print 'Finished'
INSERT INTO #stats
SELECT 'SUBSTRING test against clustered indexed column'
, @MAX
, count(*) as [Matching Records]
, @START_TIME as [Start Time]
, @END_TIME as [End Time]
, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #table2
Truncate Table #table2
print 'Starting LEFT/RIGHT test against clustered indexed column'
SET @START_TIME = GETDATE()
INSERT INTO #table2
SELECT id, aClusteredIndexedColumn
FROM #table1
WHERE LEFT(aClusteredIndexedColumn, LEN(@WhatToLookFor)) = @WhatToLookFor
or
RIGHT(aClusteredIndexedColumn, LEN(@WhatToLookFor)) = @WhatToLookFor
SET @END_TIME = GETDATE()
print 'Finished'
INSERT INTO #stats
SELECT 'LEFT/RIGHT test against clustered indexed column'
, @MAX
, count(*) as [Matching Records]
, @START_TIME as [Start Time]
, @END_TIME as [End Time]
, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #table2
Truncate Table #table2
print 'Starting CHARINDEX test against clustered indexed column'
SET @START_TIME = GETDATE()
INSERT INTO #table2
SELECT id, aClusteredIndexedColumn
FROM #table1
WHERE CHARINDEX(@WhatToLookFor,SUBSTRING(aClusteredIndexedColumn,1,LEN(@WhatToLookFor)),0) > 0
or
CHARINDEX(@WhatToLookFor, SUBSTRING(aClusteredIndexedColumn, LEN(aClusteredIndexedColumn) - LEN(@WhatToLookFor) + 1, LEN(@WhatToLookFor)), 0) > 0
SET @END_TIME = GETDATE()
print 'Finished'
INSERT INTO #stats
SELECT 'CHARINDEX test against clustered indexed column'
, @MAX
, count(*) as [Matching Records]
, @START_TIME as [Start Time]
, @END_TIME as [End Time]
, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #table2
Truncate Table #table2
INSERT INTO #stats
VALUES ('--------',null,null,null,null,'--------')
--against nonclustered indexed column ----------------------------------------
print 'Starting LIKE test against nonclustered indexed column'
SET @START_TIME = GETDATE()
INSERT INTO #table2
SELECT id, aNonClusteredIndexedColumn
FROM #table1
WHERE aNonClusteredIndexedColumn like @WhatToLookFor + '%'
or
aNonClusteredIndexedColumn like '%' + @WhatToLookFor
SET @END_TIME = GETDATE()
print 'Finished'
INSERT INTO #stats
SELECT 'LIKE test against Nonclustered indexed column'
, @MAX
, count(*) as [Matching Records]
, @START_TIME as [Start Time]
, @END_TIME as [End Time]
, CAST(DATEDIFF(MILLISECOND
, @START_TIME
,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #table2
Truncate table #table2
print 'Starting SUBSTRING test against Nonclustered indexed column'
SET @Start_Time = GETDATE()
INSERT INTO #table2
SELECT id, aNonClusteredIndexedColumn
FROM #table1
WHERE SUBSTRING(aNonClusteredIndexedColumn,1, LEN(@WhatToLookFor)) = @WhatToLookFor
or
SUBSTRING(aNonClusteredIndexedColumn, LEN(aNonClusteredIndexedColumn) - LEN(@WhatToLookFor) + 1, LEN(@WhatToLookFor)) = @WhatToLookFor
SET @END_TIME = GETDATE()
print 'Finished'
INSERT INTO #stats
SELECT 'SUBSTRING test against Nonclustered indexed column'
, @MAX
, count(*) as [Matching Records]
, @START_TIME as [Start Time]
, @END_TIME as [End Time]
, CAST(DATEDIFF(MILLISECOND , @START_TIME ,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #table2
Truncate table #table2
print 'Starting LEFT/RIGHT test against Nonclustered indexed column'
SET @Start_Time = GETDATE()
INSERT INTO #table2
SELECT id, aNonClusteredIndexedColumn
FROM #table1
WHERE LEFT(aNonClusteredIndexedColumn, LEN(@WhatToLookFor)) = @WhatToLookFor
or
RIGHT(aNonClusteredIndexedColumn, LEN(@WhatToLookFor)) = @WhatToLookFor
SET @END_TIME = GETDATE()
print 'Finished'
INSERT INTO #stats
SELECT 'LEFT/RIGHT test against Nonclustered indexed column'
, @MAX
, count(*) as [Matching Records]
, @START_TIME as [Start Time]
, @END_TIME as [End Time]
, CAST(DATEDIFF(MILLISECOND , @START_TIME ,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #table2
Truncate table #table2
print 'Starting CHARINDEX test against nonclustered indexed column'
SET @Start_Time = GETDATE()
INSERT INTO #table2
SELECT id, aNonClusteredIndexedColumn
FROM #table1
WHERE CHARINDEX(@WhatToLookFor,SUBSTRING(aNonClusteredIndexedColumn, 1, LEN(@WhatToLookFor)),0) > 0
or
CHARINDEX(@WhatToLookFor, SUBSTRING(aNonClusteredIndexedColumn, LEN(aNonClusteredIndexedColumn) - LEN(@WhatToLookFor) + 1, LEN(@WhatToLookFor)),0) > 0
SET @END_TIME = GETDATE()
print 'Finished'
INSERT INTO #stats
SELECT 'CHARINDEX test against Nonclustered indexed column'
, @MAX
, count(*) as [Matching Records]
, @START_TIME as [Start Time]
, @END_TIME as [End Time]
, CAST(DATEDIFF(MILLISECOND , @START_TIME ,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #table2
Truncate table #table2
INSERT INTO #stats
VALUES ('--------',null,null,null,null,'--------')
--Now against non indexed column --------------------------------------
print 'Starting LIKE test against NON-indexed column'
SET @Start_Time = GETDATE()
INSERT INTO #table2
SELECT id, aNonIndexedColumn
FROM #table1
WHERE aNonIndexedColumn like @WhatToLookFor + '%'
or
aNonIndexedColumn like '%' + @WhatToLookFor
SET @END_TIME = GETDATE()
print 'Finished'
INSERT INTO #stats
SELECT
'LIKE test against NON-indexed column'
, @MAX
, count(*) as [Matching Records]
, @START_TIME as [Start Time]
, @END_TIME as [End Time]
, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #table2
Truncate table #table2
print 'Starting SUBSTRING test against NON-indexed column'
SET @Start_Time = GETDATE()
INSERT INTO #table2
SELECT id, aNonIndexedColumn
FROM #table1
WHERE SUBSTRING(aNonIndexedColumn, 1, LEN(@WhatToLookFor)) = @WhatToLookFor
or
SUBSTRING(aNonIndexedColumn, LEN(aNonIndexedColumn) - LEN(@WhatToLookFor) + 1, LEN(@WhatToLookFor)) = @WhatToLookFor
SET @END_TIME = GETDATE()
print 'Finished'
INSERT INTO #stats
SELECT 'SUBSTRING test against NON-indexed column'
, @MAX
, count(*) as [Matching Records]
, @START_TIME as [Start Time]
, @END_TIME as [End Time]
, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #table2
Truncate table #table2
print 'Starting LEFT/RIGHT test against NON-indexed column'
SET @Start_Time = GETDATE()
INSERT INTO #table2
SELECT id, aNonIndexedColumn
FROM #table1
WHERE LEFT(aNonIndexedColumn, LEN(@WhatToLookFor)) = @WhatToLookFor
or
RIGHT(aNonIndexedColumn,LEN(@WhatToLookFor)) = @WhatToLookFor
SET @END_TIME = GETDATE()
print 'Finished'
INSERT INTO #stats
SELECT
'LEFT/RIGHT test against NON-indexed column'
,@MAX
,count(*) as [Matching Records]
, @START_TIME as [Start Time]
, @END_TIME as [End Time]
, CAST(DATEDIFF(MILLISECOND,@START_TIME,@END_TIME)as varchar) + ' milliseconds' as [Time to Run]
FROM #table2
Truncate table #table2
print 'Starting CHARINDEX test against NON-indexed column'
SET @Start_Time = GETDATE()
INSERT INTO #table2
SELECT id, aNonIndexedColumn
FROM #table1
WHERE
CHARINDEX(@WhatToLookFor,SUBSTRING(aNonIndexedColumn,1, LEN(@WhatToLookFor)),0) > 0
or
CHARINDEX(@WhatToLookFor, SUBSTRING(aNonIndexedColumn, LEN(aNonIndexedColumn) - LEN(@WhatToLookFor) + 1,LEN(@WhatToLookFor)),0) > 0
SET @END_TIME = GETDATE()
print 'Finished'
INSERT INTO #stats
SELECT 'CHARINDEX test against NON-indexed column'
, @MAX
, count(*) as [Matching Records]
, @START_TIME as [Start Time]
, @END_TIME as [End Time]
, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #table2
Truncate table #table2
INSERT INTO #stats
VALUES ('--------',null,null,null,null,'--------')
--Now do normal string comparisons ------------------------------------
SET NOCOUNT ON
print 'Starting Looping string tests...'
DECLARE @LikeTimeDiff int = 0
DECLARE @SubstringTimeDiff int = 0
DECLARE @LeftRightTimeDiff int = 0
DECLARE @CharindexTimeDiff int = 0
DECLARE @LikeMatches int = 0
DECLARE @SubstringMatches int = 0
DECLARE @LeftRightMatches int = 0
DECLARE @CharindexMatches int = 0
Set @Counter = 1
WHILE (@Counter <= @MAX)
BEGIN
Set @tempString =(Select aClusteredIndexedColumn FROM #table1 where id = @Counter)
--Like
SET @Start_Time = GETDATE()
if (@tempString like @WhatToLookFor + '%'
or
@tempString like '%' + @WhatToLookFor)
BEGIN
SET @END_TIME = GETDATE()
SET @LikeMatches = @LikeMatches + 1
END
ELSE
BEGIN
SET @END_TIME = GETDATE()
END
SET @LikeTimeDiff = @LikeTimeDiff + DATEDIFF(MILLISECOND, @START_TIME,@END_TIME)
--Substring
SET @Start_Time = GETDATE()
if (SUBSTRING(@tempString, 1, LEN(@WhatToLookFor)) = @WhatToLookFor
or
SUBSTRING(@tempString, LEN(@tempString) - LEN(@WhatToLookFor) + 1, LEN(@WhatToLookFor)) = @WhatToLookFor)
BEGIN
SET @END_TIME = GETDATE()
SET @SubstringMatches = @SubstringMatches + 1
END
ELSE
BEGIN
SET @END_TIME = GETDATE()
END
SET @SubstringTimeDiff = @SubstringTimeDiff + DATEDIFF(MILLISECOND, @START_TIME,@END_TIME)
--Left/Right
SET @Start_Time = GETDATE()
if (LEFT(@tempString, LEN(@WhatToLookFor)) = @WhatToLookFor
or
RIGHT(@tempString, LEN(@WhatToLookFor)) = @WhatToLookFor)
BEGIN
SET @END_TIME = GETDATE()
SET @LeftRightMatches = @LeftRightMatches + 1
END
ELSE
BEGIN
SET @END_TIME = GETDATE()
END
SET @LeftRightTimeDiff = @LeftRightTimeDiff + DATEDIFF(MILLISECOND, @START_TIME,@END_TIME)
--Charindex
SET @Start_Time = GETDATE()
if (CHARINDEX(@WhatToLookFor,SUBSTRING(@tempString, 1, LEN(@WhatToLookFor)),0) > 0
or
CHARINDEX(@WhatToLookFor, SUBSTRING(@tempString, LEN(@tempString) - LEN(@WhatToLookFor) + 1, LEN(@WhatToLookFor)), 0) > 0)
BEGIN
SET @END_TIME = GETDATE()
SET @CharindexMatches = @CharindexMatches + 1
END
ELSE
BEGIN
SET @END_TIME = GETDATE()
END
SET @CharindexTimeDiff = @CharindexTimeDiff + DATEDIFF(MILLISECOND, @START_TIME,@END_TIME)
SET @Counter = @Counter + 1
END
print 'Finished looping string tests...'
INSERT INTO #stats
SELECT 'LIKE string test'
, @MAX
, @LikeMatches as [Matching Records]
,null
,null
, CAST(@LikeTimeDiff as varchar) + ' milliseconds' as [Time to Run]
INSERT INTO #stats
SELECT 'SUBSTRING string test'
, @MAX
, @SubstringMatches as [Matching Records]
,null
,null
,CAST(@SubstringTimeDiff as varchar) + ' milliseconds' as [Time to Run]
INSERT INTO #stats
SELECT 'LEFT/RIGHT string test'
, @MAX
, @LeftRightMatches as [Matching Records]
,null
,null
,CAST(@LeftRightTimeDiff as varchar) + ' milliseconds' as [Time to Run]
INSERT INTO #stats
SELECT 'CHARINDEX string test'
, @MAX
, @CharindexMatches as [Matching Records]
,null
,null
,CAST(@CharindexTimeDiff as varchar) + ' milliseconds' as [Time to Run]
SET NOCOUNT OFF
--Display the results
select *
from #stats
order by id
--clean up
IF OBJECT_ID('tempdb..#table1') IS NOT NULL
DROP TABLE #table1
IF OBJECT_ID('tempdb..#table2') IS NOT NULL
DROP TABLE #table2
IF OBJECT_ID('tempdb..#stats') IS NOT NULL
DROP TABLE #stats
|
8224
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
