Slow select on sql 2000 with like '%value'

[Benoit Le Goff]

Hello.

I test some query on sql server 2000 (sp2 on OS windows 2000) and i
want to know why a simple query like this :
select * from Table Where Column like '%value'
is more slow on 2000 than on sql 7.

And this case arrive only if the % character is in the begin.
If you test this :
select * from Table Where Column like 'v%alue'
then it's more fast on 2000.

I look the execution plan, there is a difference but this difference
is the same in all the case i test (for the two query i write in this
message for example).

I don't understand why and so, if someone have an explanation, and
perhaps a solution ...

Excuse my poor english language.
And thanks for time people spend to answer me.

[Simon Hayes]

"Benoit Le Goff" <be***@caramail.com> wrote in message
news:b4**************************@posting.google.c om...

Hello.

I test some query on sql server 2000 (sp2 on OS windows 2000) and i
want to know why a simple query like this :
select * from Table Where Column like '%value'
is more slow on 2000 than on sql 7.

And this case arrive only if the % character is in the begin.
If you test this :
select * from Table Where Column like 'v%alue'
then it's more fast on 2000.

I look the execution plan, there is a difference but this difference
is the same in all the case i test (for the two query i write in this
message for example).

I don't understand why and so, if someone have an explanation, and
perhaps a solution ...

Excuse my poor english language.
And thanks for time people spend to answer me.

I don't know why you're seeing a performance difference between SQL7 and
SQL2000, but a query like "select * from Table Where Column like '%value'"
will often be slow. This is because you're looking for a match at the end of
the column value, so an index can't be used. On the other hand, with the
query "select * from Table Where Column like 'v%alue'", SQL Server could use
a suitable index to examine only rows where the string starts with 'v'.

Simon

[Erland Sommarskog]

Benoit Le Goff (be***@caramail.com) writes:

I test some query on sql server 2000 (sp2 on OS windows 2000) and i
want to know why a simple query like this :
select * from Table Where Column like '%value'
is more slow on 2000 than on sql 7.

And this case arrive only if the % character is in the begin.
If you test this :
select * from Table Where Column like 'v%alue'
then it's more fast on 2000.

I look the execution plan, there is a difference but this difference
is the same in all the case i test (for the two query i write in this
message for example).

I can't really say why the query is slower on SQL2000 than on SQL7, but
it could be that you have more data on SQL 2000, or that the table is
very fragmented. You could run DBCC DBREINDEX on the table, and see what
effect it has.

The expression LIKE '%value' is likely to be slow, because if there is
an index on the column, SQL Server can not search it, because the
matching values could be anywhere in the index. So the normal thing for
SQL Server to do would be to scan the table. Which is why you can suffer
from fragmentation.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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