開發(fā)人員經(jīng)常需要在數(shù)據(jù)庫表中查詢以某種文本模式開始和/或結(jié)束的記錄。例如，查找名字以“DAV”開頭的所有員工。
根據(jù)我的經(jīng)驗，SQL 開發(fā)人員通常依賴 4 個常用函數(shù)來實現(xiàn)這一點。
就在那時，這位好奇的顧問決定將它們正面交鋒：LIKE?vs?SUBSTRING?vs?LEFT?/?RIGHT?vs?CHARINDEX，看看哪個最快。
?文章來源地址http://www.zghlxwxcb.cn/news/detail-454238.html
賽前秀
出于測試目的，使用 Microsoft SQL Server 2014 在具有 128GB 內(nèi)存、16 核 CPU、額定頻率為 2.54 Ghz 的 Windows 2012 Server 上執(zhí)行 SQL 代碼。
?文章來源：http://www.zghlxwxcb.cn/news/detail-454238.html
為了讓事情變得更有趣，測試分為兩部分：
- 將測試每個對表的速度
- 將根據(jù)常規(guī)“字符串”數(shù)據(jù)測試每個的速度
?
這里的假設(shè)是不會有競爭條件或?qū)Υ?SQL 代碼的多線程調(diào)用。這只是一個直接的、正面的測試。
為確保 SQL Server 不會緩存任何查詢（或與此相關(guān)的任何內(nèi)容），在每次測試之前運行以下代碼：
|
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
|
創(chuàng)建并填充了兩個表。主鍵相同。每個表中的三列將包含完全相同的數(shù)據(jù)，但是：
- 一個將有一個聚集索引
- 另一個將有一個非聚集索引
- 第三個沒有索引
只是看看它們是否對性能有任何影響。
|
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.'
|
使用每個函數(shù)完成查詢以搜索插入到唯一標(biāo)識符(guid) 中的特定字符串。結(jié)果被插入到輔助表中，以確保每個測試的結(jié)果數(shù)量相同。
完整的源代碼可以在本文末尾找到。
?
您是博彩個人嗎？
這是我們的LIKE?vs?SUBSTRING?vs?LEFT?/?RIGHT?vs?CHARINDEX速度測試的結(jié)果，以毫秒為單位。
獲勝者以綠色突出顯示。在這個速度測試中沒有第二名的分?jǐn)?shù)。
| 功能 |
對聚集索引列執(zhí)行的時間，以毫秒為單位，超過 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 |
| 3：左/右 |
40, 43, 43 |
406, 406, 410 |
2523, 2526, 2516 |
24713, 24770, 24823 |
| 4：CHARINDEX |
10, 10, 10 |
56, 56, 56 |
590, 593, 576 |
5713, 5683, 5730 |
?
| 功能 |
對非聚集索引列執(zhí)行的時間，以毫秒為單位，超過 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 |
| 3：左/右 |
100, 86, 100 |
253, 246, 250 |
2730, 2733, 2730 |
27166, 26633, 27123 |
| 4：CHARINDEX |
10, 10, 10 |
56, 53, 60 |
590, 590, 586 |
5810, 5763, 5690 |
?
| 功能 |
對非索引列執(zhí)行的時間，以毫秒為單位，超過 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 |
| 3：左/右 |
46, 46, 43 |
246, 250, 253 |
2800, 2496, 2546 |
24690, 24750, 24810 |
| 4：CHARINDEX |
10, 6, 6 |
56, 56, 60 |
576, 590, 573 |
5790, 5836, 6276 |
?
| 功能 |
對 varchar 字符串執(zhí)行的時間，以毫秒為單位，超過 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 |
| 3：左/右 |
45, 45, 24 |
474, 396, 351 |
3945, 4044, 3990 |
39969, 39135, 40919 |
| 4：CHARINDEX |
60, 36, 66 |
618, 636, 564 |
5766, 5937, 5904 |
59773, 58412, 60198 |
?
看看誰擁有它！
在查詢表列以查找值時，CHARINDEX 顯然是無可爭議的王者。其他 3 個中的 2 個甚至沒有接近我承認(rèn)他們是“競爭對手”的速度。我預(yù)計 LIKE 會做得更好，尤其是在索引列上，但對 CHARINDEX 的統(tǒng)治感到非常驚訝。
在搜索 varchar/string 變量時，LEFT/RIGHT 位居榜首。
?
簡而言之，當(dāng)您需要在數(shù)據(jù)的開頭或結(jié)尾搜索子字符串時：
- 對表列執(zhí)行查詢時，使用 CHARINDEX
- 在 @varchar 字符串變量中搜索時，使用 LEFT/RIGHT
?
我在下面為您留下了 SQL 代碼，因此請隨意將其用作執(zhí)行您自己的性能基準(zhǔn)測試的基礎(chǔ)。
如果您有任何建議或其他方式，請在下面發(fā)表評論并分享知識！
?
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
|
?
到了這里，關(guān)于SQL Server 查找字符串LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
![[ERR] [22001] [Microsoft][SQL Server Native Client 10.0][SQL Server]將截斷字符串或二進制數(shù)據(jù)](https://imgs.yssmx.com/Uploads/2024/02/523926-1.jpeg)
![[Microsoft] [SQL Server的ODBC驅(qū)動程序11] SQL Server網(wǎng)絡(luò)接口：連接字符串無效[87] [Microsoft] [SQL Server的ODBC驅(qū)動程序](https://imgs.yssmx.com/Uploads/2024/02/688812-1.png)
