The Pre-Game Show

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:

SQL to clear cache/buffers

Transact-SQL

1

2

3

4

5

6

7

8

9

10

checkpoint

go

DBCCDROPCLEANBUFFERS

go

DBCCFREESESSIONCACHE

go

DBCCFREEPROCCACHE

go

DBCCFREESYSTEMCACHE('ALL')

go

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 tables

Transact-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

--create temp table to hold all generated data

IFOBJECT_ID('tempdb..#table1')ISNOTNULL

DROPTABLE#table1

CreateTable#table1

(

idintidentity(1,1),

aGuiduniqueidentifiernotnull,

aClusteredIndexedColumnvarchar(900)notnull,

aNonClusteredIndexedColumnvarchar(900)notnull,

thecountintnotnull,

aNonIndexedColumnvarchar(900)notnull

)

--create temp table to hold test results

IFOBJECT_ID('tempdb..#table2')ISNOTNULL

DROPTABLE#table2

CreateTable#table2

(

idintnotnull,

matchedDatavarchar(900)notnull

)

--create the indexes

printCAST(GETDATE()asvarchar)+' Started creating temptable indexes.'

CREATECLUSTEREDINDEXIDX_clON#table1 (aClusteredIndexedColumn)

CREATENONCLUSTEREDINDEXIDX_nonclON#table1(aNonClusteredIndexedColumn)

CREATENONCLUSTEREDINDEXIDX_idON#table1(id)

printCAST(GETDATE()asvarchar)+' 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.

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!