Microsoft SQL Server forum

Author

Like vs =

John

2005-08-19, 8:23 pm

Hi

Is there a major speed difference if WHERE col like 'abc%' is used against
WHERE col = 'abc' in a select statement in stored procedure?

Thanks

Regards

Tom Moreau

2005-08-19, 8:23 pm

Ideally, if you are looking for exact matches, then avoid LIKE, and go with
=. The optimizer knows that it doesn't have to go for anything but 'abc'.
The speed difference should be noticeable with larger tables.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@nospam.infovis.co.uk> wrote in message
news:%23FoxyhRpFHA.2976@TK2MSFTNGP12.phx.gbl...
Hi

Is there a major speed difference if WHERE col like 'abc%' is used against
WHERE col = 'abc' in a select statement in stored procedure?

Thanks

Regards

Erland Sommarskog

2005-08-20, 7:23 am

John (John@nospam.infovis.co.uk) writes:
> Is there a major speed difference if WHERE col like 'abc%' is used against
> WHERE col = 'abc' in a select statement in stored procedure?

It can be virtually difference at all, or it can like night and day.

If col is non-indexed, the only overhead is a somewhat more complex
comparison operation.

If col is indexed with a non-clustered index, the optimizer will have
to make a decision on whether to use the index. Assume for simplicity
that the index is unique. Then = will use that index, and access will be
fast. But if there is LIKE there is another story. In this case, there
are two possible strategies: use the index or scan the table. Scanning
the table is better a large proportion of the rows start with 'abc%'.
The optimizer does not know about this, but from statistics compiled
about the table, it can make an estimate. If the estimate is incorrect,
the plan may not be the best - something the users will very painfully
notice.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

John

2005-08-20, 1:23 pm

Hi

My problem is that I have three parameters and all of them optional. I can't
think of avoiding writing a sub-sp for every combination of provided and not
provided parameters except to use like. Is there a better to handle three
parameters where any combination of them could be optional? I am using the
following select in the sp;

CREATE PROCEDURE [dbo].[MyProc]
@Param1 varchar(50) ='',
@Param2 varchar(50) ='',
@Param3 varchar(50) =''
AS
SET NOCOUNT ON;
SELECT <fieldlist>
FROM MyTable
WHERE (Col1 like @Param1+ '%') AND (Col2 like @Param2+ '%') AND (Col3 like
@Param3+ '%')

Thanks

Regards

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns96B86CFBDFA9
9Yazorman@127.0.0.1...
> John (John@nospam.infovis.co.uk) writes:
>
> It can be virtually difference at all, or it can like night and day.
>
> If col is non-indexed, the only overhead is a somewhat more complex
> comparison operation.
>
> If col is indexed with a non-clustered index, the optimizer will have
> to make a decision on whether to use the index. Assume for simplicity
> that the index is unique. Then = will use that index, and access will be
> fast. But if there is LIKE there is another story. In this case, there
> are two possible strategies: use the index or scan the table. Scanning
> the table is better a large proportion of the rows start with 'abc%'.
> The optimizer does not know about this, but from statistics compiled
> about the table, it can make an estimate. If the estimate is incorrect,
> the plan may not be the best - something the users will very painfully
> notice.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>

Erland Sommarskog

2005-08-20, 8:23 pm

John (John@nospam.infovis.co.uk) writes:
> My problem is that I have three parameters and all of them optional. I
> can't think of avoiding writing a sub-sp for every combination of
> provided and not provided parameters except to use like. Is there a
> better to handle three parameters where any combination of them could be
> optional? I am using the following select in the sp;
>
> CREATE PROCEDURE [dbo].[MyProc]
> @Param1 varchar(50) ='',
> @Param2 varchar(50) ='',
> @Param3 varchar(50) =''
> AS
> SET NOCOUNT ON;
> SELECT <fieldlist>
> FROM MyTable
> WHERE (Col1 like @Param1+ '%') AND (Col2 like @Param2+ '%') AND (Col3 like
> @Param3+ '%')

Aha! Again it is proven that if you don't supply the full information,
you may not get a very appropriate answer. I figured that may be the
user could enter part of a name, you were wondering whether if was good
to use LIKE as a matter of routine. (For that case, I would suggest to
search for equality first, and for LIKE searches require at least two-
three characters of input.)

This is a different problem. And a more complex one, that has several
solutions, and which one to pick depends on the situation. Now, I am
not going to tell all this here. Instead I will refer you to an
longer article on the topic: http://www.sommarskog.se/dyn-search.html.

From other posts I have understood that you are looking at SQL 2005.
Therefore I should add that the article is written with SQL 2000 in
mind. I know of a things that I need to update. Nevertheless the article
should be useful to you.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

--CELKO--

2005-08-21, 9:24 am

>> Is there a better to handle three parameters where any combination of them could be optional? <<

Let the inputs be NULL when missing and coalesce them to empty strings:

WHERE col1 LIKE COALESCE (@param1, '') + '%'
AND col2 LIKE COALESCE (@param2, '') + '%'
AND col3 LIKE COALESCE (@param3, '') + '%'

Erland Sommarskog

2005-08-21, 11:23 am

--CELKO-- (jcelko212@earthlink
.net) writes:
them could be optional? <<[color=darkred]
>
> Let the inputs be NULL when missing and coalesce them to empty strings:
>
> WHERE col1 LIKE COALESCE (@param1, '') + '%'
> AND col2 LIKE COALESCE (@param2, '') + '%'
> AND col3 LIKE COALESCE (@param3, '') + '%'

I agree that NULL is better than empty strings from a conceptual point of
view, but performance-wise it will not make things any better.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links