In(..) and Like xxxx% performance

[Mark Quest]

Hi,
I am experiencing strange SQL server behavior,

The table has an index on (filed1, field2) - which is primary key.
if I do select where field1 = 'a' and field2 like '123%' runs fast.
if I do select where field1 = 'b' and field2 like '123%' runs fast
too.

if I do select where field1 in ('a','b') and field2 like '123%' the
damn thing runs forever.

What is interesting, the SQL plan is perfectly using primary index and
selection is translated into "good" ranges for field2. It fills like
server does not take in consideration that field2 is in index.

Any ideas what's going on? I'd hate to use two selects to avoid
problem.

Thank you.
M.Q.

[Erland Sommarskog]

Re: In(..) and Like xxxx% performance

Mark Quest (nospamhere2@ya hoo.com) writes:[color=blue]
> I am experiencing strange SQL server behavior,
>
> The table has an index on (filed1, field2) - which is primary key.
> if I do select where field1 = 'a' and field2 like '123%' runs fast.
> if I do select where field1 = 'b' and field2 like '123%' runs fast
> too.
>
> if I do select where field1 in ('a','b') and field2 like '123%' the
> damn thing runs forever.
>
> What is interesting, the SQL plan is perfectly using primary index and
> selection is translated into "good" ranges for field2. It fills like
> server does not take in consideration that field2 is in index.
>
> Any ideas what's going on? I'd hate to use two selects to avoid
> problem.[/color]

Sounds a bit funny, but without knowledge of the table and query plan,
it's a little bit difficult to say what is going on.

If you can demonstrate the situation with a script that anyone could
copy and paste and run on his machine, that would be something to bring
up with Microsoft. But I would guess that it is not that simple to
reproduce.

But maybe you could post the CREATE TABLE and CREATE INDEX statements
for the table, and also give some indication of sizes and data distribution?
And the exact text of the slow query as well as the output from
SET SHOWPLAN_ALL ON for the query.

A faint possibility is that there is blocking or corruption. Check the
latter with DBCC CHECKDB.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp