I expected LIKE to do better, especially on index columns, but was quite surprised by CHARINDEX’s domination. The 2 of the other 3 didn’t even come close in speed for me to acknowledge them as “competition”. Time to execute against varchar string, in milliseconds, over 3 runs:ĬHARINDEX is clearly the undisputed king when it comes to querying a table column looking for a value. Time to execute against Non-Indexed column, in milliseconds, over 3 runs: Time to execute against Non-Clustered Indexed column, in milliseconds, over 3 runs: Time to execute against Clustered Indexed column, in milliseconds, over 3 runs: There are no points for second place in this speed test. Here are the results, in milli-seconds, in our LIKE vs SUBSTRING vs LEFT/ RIGHT vs CHARINDEX speed tests. The complete source code can be found at the end of this article. The results are inserted into the secondary table to make sure we have the same number of results for each test. Print CAST ( GETDATE ( ) as varchar ) + ' Finished creating temptable indexes.'Ī query is done using each function to search for a particular string inserted within a UniqueIdentifier (guid). To ensure SQL Server didn’t keep any queries (or anything for that matter) cached, the following code was run before each test: This is just a straight up, head-to-head test. The assumption here is there will be no race conditions or multi-threaded calls to this SQL code. will test the speed of each against regular “string” data.will test the speed of each against table table.To make things a bit more interesting, the test is split into two parts: 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.įor 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. In my experience, there tends to be 4 common functions SQL developers rely upon for this. For instance, find all employees where their first name begins with “DAV”. LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEXĪll too often developers need to query a database table for records which begin and/or end with a certain textual pattern.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |