Spread the love

LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX

All too often developers need to query a database table for records which begin and/or end with a certain textual pattern. For instance, find all employees where their first name begins with “DAV”.

In my experience, there tends to be 4 common functions SQL developers rely upon for this.

That’s when this curious consultant decided to put them head-to-head: LIKE vs SUBSTRING vs LEFT / RIGHT vs CHARINDEX to see which is the fastest.

The Pre-Game Show

For testing purposes, the SQL code was executed on a Windows 2012 Server with 128GB memory, 16 core CPU rated at 2.54 Ghz, using Microsoft SQL Server 2014.

To make things a bit more interesting, the test is split into two parts:

will test the speed of each against table table
will test the speed of each against regular “string” data

The assumption here is there will be no race conditions or multi-threaded calls to this SQL code. This is just a straight up, head-to-head test.

To ensure SQL Server didn’t keep any queries (or anything for that matter) cached, the following code was run before each test:

checkpoint
go
DBCC DROPCLEANBUFFERS
go
DBCC FREESESSIONCACHE
go
DBCC FREEPROCCACHE
go
DBCC FREESYSTEMCACHE('ALL')
go

checkpoint

DBCC DROPCLEANBUFFERS

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE

DBCC FREESYSTEMCACHE('ALL')

Two tables are created, and populated. The primary keys are the same. Three columns in each table will hold the exact same data, but:

one will have a clustered index
another will have a non-clustered index
the third will have no index

just to see if they have any effect on performance.

--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.'

--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.'

A query is done using each function to search for a particular string inserted within a UniqueIdentifier (guid). The results are inserted into the secondary table to make sure we have the same number of results for each test.

The complete source code can be found at the end of this article.

Are you a Betting Individual?

Here are the results, in milli-seconds, in our LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX speed tests.

The winner is highlighted in green. There are no points for second place in this speed test.

Function	Time to execute against Clustered Indexed column, in milliseconds, over 3 runs:
# Records:	50,000	500,000	5,000,000	50,000,000
1: LIKE	46, 43, 40	406, 413, 403	4016, 3986, 3996	39940, 39756, 40423
2: SUBSTRING	46, 46, 46	440, 443, 443	2513, 2513, 2603	24760, 24873, 24270
3: LEFT/RIGHT	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

Function	Time to execute against Non-Clustered Indexed column, in milliseconds, over 3 runs:
# Records:	50,000	500,000	5,000,000	50,000,000
1: LIKE	43, 40, 40	70, 63, 73	680, 666, 670	7203, 6756, 6716
2: SUBSTRING	103, 100, 103	256, 256, 260	2750, 2750, 2763	27076, 26940, 27053
3: LEFT/RIGHT	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

Function	Time to execute against Non-Indexed column, in milliseconds, over 3 runs:
# Records:	50,000	500,000	5,000,000	50,000,000
1: LIKE	43, 43, 40	63, 66, 70	670, 676, 663	6790, 7153, 6726
2: SUBSTRING	126, 123, 123	396, 396, 396	3016, 2810, 2933	25463, 25643, 25420
3: LEFT/RIGHT	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

Function	Time to execute against varchar string, in milliseconds, over 3 runs:
# Records:	50,000	500,000	5,000,000	50,000,000
1: LIKE	126, 87, 75	909, 882, 822	8358, 8607, 8667	88610, 87349, 85341
2: SUBSTRING	60, 45, 69	585, 582, 576	5571, 5673, 5670	57849, 54552, 56344
3: LEFT/RIGHT	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

Look Who Owned It!

CHARINDEX is clearly the undisputed king when it comes to querying a table column looking for a value. The 2 of the other 3 didn’t even come close in speed for me to acknowledge them as “competition”. I expected LIKE to do better, especially on index columns, but was quite surprised by CHARINDEX’s domination.

When it comes to searching a varchar/string variable, LEFT/RIGHT commanded the top spot.

In a nutshell, when you need to search for a substring at the beginning or end of data:

when performing a query against a table column, use CHARINDEX
when searching within a @varchar string variable, use LEFT/RIGHT

I’ve left you the SQL code below, so feel free to use it as a basis for conducting your own performance benchmarks.

Leave a comment below and share the knowledge if you have any suggestions or other ways of doing this!

The SQL Source Code

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

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

DBCC DROPCLEANBUFFERS

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE

DBCC FREESYSTEMCACHE('ALL')

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

,@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 + '%'

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

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

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

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 + '%'

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

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

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

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 + '%'

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

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

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

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 + '%'

@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

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

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

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

, CAST(@LikeTimeDiff as varchar) + ' milliseconds' as [Time to Run]

INSERT INTO #stats

SELECT 'SUBSTRING string test'

, @MAX

, @SubstringMatches as [Matching Records]

,null

,CAST(@SubstringTimeDiff as varchar) + ' milliseconds' as [Time to Run]

INSERT INTO #stats

SELECT 'LEFT/RIGHT string test'

, @MAX

, @LeftRightMatches as [Matching Records]

,null

,CAST(@LeftRightTimeDiff as varchar) + ' milliseconds' as [Time to Run]

INSERT INTO #stats

SELECT 'CHARINDEX string test'

, @MAX

, @CharindexMatches as [Matching Records]

,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

Spread the love

SQL: LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX

LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX

The Pre-Game Show

Are you a Betting Individual?

Look Who Owned It!

The SQL Source Code

David Lozinski