like Vs '=' Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

like Vs '=' Performance

Author

Topic

abuhassan

105 Posts

Posted - 2006-12-04 : 10:20:46

Hi

I wanted to find out which is faster in terms of performance:
e.g.

select * from orders where orderRef = '00093'

select * from orders where orderRef like '00093'

I know there is a differnece if i use the wild cards % etc in the results but i wanted to find out with regards to the queries above?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 10:22:46

Time-test them.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-04 : 10:24:40

there is none.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-04 : 10:25:52

Set the execution plan and test

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-04 : 10:28:01

exec plans are the same.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

abuhassan

105 Posts

Posted - 2006-12-04 : 10:29:02

I looked at the time in query analyser they seem to be the same, with the same results retrived.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 10:29:50

That's my point. Try to do some investigation first before asking.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-12-04 : 11:09:25

Is the Plan going to be the same for:

select * from orders where orderRef = @MyParameter

and

select * from orders where orderRef LIKE @MyParameter

assuming that the Plan is cached (e.g. in an Sproc)?

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-04 : 11:14:18

It does have slight performance difference in case when there unique non-clustered index on the column on which you are searching.

Check out the following sample data:

if exists(select * from information_schema.tables where table_name = 't' and table_type = 'BASE TABLE')
	drop table t
go

create table t
(
	i int identity(1,1),
	x varchar(100) unique nonclustered
)
go

insert t
select 'aa' union all
select 'bb'
go

select * from t    -- query 1
where x like 'aa'

select * from t    -- query 2
where x = 'aa'
go

I get Bookmark lookup cost for the first query to be 2% while for the second it is 1%.

Harsh Athalye
India.
"Nothing is Impossible"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-04 : 11:26:33

those costs don't give you any real information if you ask me.
take a look at the numbers in the window of each lokup and see if they differ.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-04 : 11:29:14

Do you mean overall query cost?

It differs but with a negligible margin - 50.01% in first case and 49.99% in second.

Harsh Athalye
India.
"Nothing is Impossible"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-04 : 11:35:59

no i mean when you hoover with the mouse iver the lookup image a tooltip appears with some data
if that data differes then the lookups are different.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-04 : 11:47:57

No, there is no difference...but interesting thing is that Estimated row size differs for both the plans...does that matters?

Harsh Athalye
India.
"Nothing is Impossible"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-04 : 11:50:19

try updating your statistics on the table

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources