Query Performance Comparison

Query Performance Comparison

What is faster: two LIKE statements or one BETWEEN?

((MyData LIKE '02105_') OR ((MyData LIKE '02106_)MyData BETWEEN '021050' AND '021069'

Please advise.

When trying to decide which is the faster SQL for a particular query, my philosophy is to test the two options rather than making an educated guess.

Using version 7, I created a table with 100,000 rows with data similar to your example. Then I turned on show stats i/o and ran the two versions you provided. There was absolutely no difference in performance. That makes sense because, in both cases, the optimizer took advantage of an index on that column.

However, you should realize that your queries are not exactly the same. The LIKE keyword will return a match on the value ‘02105’, while the query using the BETWEEN statement will skip that row.

Here is the code I used to test your queries:

use pubsgoset nocount ongoif exists(select * from sysobjects where type = 'U' and name = 'liketest')   drop table liketestgocreate table liketest(   i char(6) constraint pk_liketest        primary key (i))godeclare @i intselect @i = 0while @i < 100000begin     insert into liketest (i) values ( convert(char,(floor(rand(@i)* 1000000)),0))         select @i = @i + 1endselect * fromliketest wherei LIKE '12105_' OR i LIKE '12106_'select *from liketest wherei BETWEEN '121050' AND '121069'

See also  Professionalism Starts in Your Inbox: Keys to Presenting Your Best Self in Email

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist