Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Mar 21, 2000

Query Performance Comparison

Question:
What is faster: two LIKE statements or one BETWEEN?
((MyData LIKE '02105_') OR ((MyData LIKE '02106_)

MyData BETWEEN '021050' AND '021069'
Please advise.

Answer:
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 pubs
go
set nocount on
go
if exists(select * from sysobjects where type = 'U' and name = 'liketest')
   drop table liketest
go

create table liketest
(
   i char(6) constraint pk_liketest
        primary key (i)
)

go
declare @i int
select @i = 0

while @i < 100000
begin
     insert into liketest (i) values ( convert(char,(floor(rand(@i)* 1000000)),0))
         select @i = @i + 1
end

select * from
liketest where
i LIKE '12105_' OR i LIKE '12106_'

select *
from liketest 
where
i BETWEEN '121050' AND '121069'
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date