I have a stored procedure that has some problems with slow performance.
The procedure has 2 parameters

@firstname varchar(100)
@lastname varchar(100)

These parameters can have values like a%, that is wildcard searches.
The strange thing is that if one of the parameters has the value %, and
the other one a%, the performance is very bad.
If i subsistute the variables with exactly the same values hardcoded in
the where-clause, the query is very fast.
If both variables has some characters prepending the percent sign, the
performance is better.

SELECT distinct u.user_id, u.username, u.status_id
FROM statusnames sn, statuses s, users u, users_persons up, persons p,
users_roles ur
WHERE p.lastname like @lastname
AND p.firstname like @firstname
AND s.status_id = u.status_id
AND sn.statusname_id = s.statusname_id
AND u.user_id = up.user_id
AND up.person_id = p.person_id

What makes SQL server behave so differently with variables and
hardcoded values?

Check your query plan in each scenario. I would think the system is
performing a scan when using a wild card. Basically when passing a % you
are in effect asking for the server to check all values. What's the
likelihood of the client application needing or passing an explicit value
versus A% versus %? You could code in three different select statements and
use a few if statements to direct the procedure. If this is high volume
query break the queries into separate procedures and call a controlling
procedure that makes sub calls. This will help cache plans.

<pragile@gmail.com> wrote in message
news:1106908332.087432.76550@c13g2000cwb.googlegro ups.com...
>I have a stored procedure that has some problems with slow performance.
> The procedure has 2 parameters
>
> @firstname varchar(100)
> @lastname varchar(100)
>
> These parameters can have values like a%, that is wildcard searches.
> The strange thing is that if one of the parameters has the value %, and
> the other one a%, the performance is very bad.
> If i subsistute the variables with exactly the same values hardcoded in
> the where-clause, the query is very fast.
> If both variables has some characters prepending the percent sign, the
> performance is better.
>
> SELECT distinct u.user_id, u.username, u.status_id
> FROM statusnames sn, statuses s, users u, users_persons up, persons p,
> users_roles ur
> WHERE p.lastname like @lastname
> AND p.firstname like @firstname
> AND s.status_id = u.status_id
> AND sn.statusname_id = s.statusname_id
> AND u.user_id = up.user_id
> AND up.person_id = p.person_id
>
> What makes SQL server behave so differently with variables and
> hardcoded values?
>

Thank you for your advice, but i think your solution adds to much
(unnecessary) complexity to the procedure.
I think the whole problem must be releated to some weaknesses in query
optimzation in SQL server, and would like to find a way to ommit them.

As supplementary information; if you have only firstname or lastname in
the where-clause, it works fine.

It's a long shot. Try running sp_updatestats then execute the SP.

Would it be any faster if you short-circuit the like?

WHERE (@lastname = '%' OR p.lastname like @lastname)
AND (@firstname = '%' OR p.firstname like @firstname)


<pragile@gmail.com> wrote in message
news:1106908332.087432.76550@c13g2000cwb.googlegro ups.com...
> I have a stored procedure that has some problems with slow performance.
> The procedure has 2 parameters
>
> @firstname varchar(100)
> @lastname varchar(100)
>
> These parameters can have values like a%, that is wildcard searches.
> The strange thing is that if one of the parameters has the value %, and
> the other one a%, the performance is very bad.
> If i subsistute the variables with exactly the same values hardcoded in
> the where-clause, the query is very fast.
> If both variables has some characters prepending the percent sign, the
> performance is better.
>
> SELECT distinct u.user_id, u.username, u.status_id
> FROM statusnames sn, statuses s, users u, users_persons up, persons p,
> users_roles ur
> WHERE p.lastname like @lastname
> AND p.firstname like @firstname
> AND s.status_id = u.status_id
> AND sn.statusname_id = s.statusname_id
> AND u.user_id = up.user_id
> AND up.person_id = p.person_id
>
> What makes SQL server behave so differently with variables and
> hardcoded values?
>

Again. Check the query plan for clues. The optimizer is all about making
the best decision given the facts. Once you know what it's choosing you can
plan a course of action.

Good luck.
<pragile@gmail.com> wrote in message
news:1106918079.503223.107990@f14g2000cwb.googlegr oups.com...
> Thank you for your advice, but i think your solution adds to much
> (unnecessary) complexity to the procedure.
> I think the whole problem must be releated to some weaknesses in query
> optimzation in SQL server, and would like to find a way to ommit them.
>
> As supplementary information; if you have only firstname or lastname in
> the where-clause, it works fine.
>

[posted and mailed, please reply in news]

(pragile@gmail.com) writes:
> I have a stored procedure that has some problems with slow performance.
> The procedure has 2 parameters
>
> @firstname varchar(100)
> @lastname varchar(100)
>
> These parameters can have values like a%, that is wildcard searches.
> The strange thing is that if one of the parameters has the value %, and
> the other one a%, the performance is very bad.
> If i subsistute the variables with exactly the same values hardcoded in
> the where-clause, the query is very fast.
> If both variables has some characters prepending the percent sign, the
> performance is better.
>
> SELECT distinct u.user_id, u.username, u.status_id
> FROM statusnames sn, statuses s, users u, users_persons up, persons p,
> users_roles ur
> WHERE p.lastname like @lastname
> AND p.firstname like @firstname
> AND s.status_id = u.status_id
> AND sn.statusname_id = s.statusname_id
> AND u.user_id = up.user_id
> AND up.person_id = p.person_id
>
> What makes SQL server behave so differently with variables and
> hardcoded values?

Time to learn something how the optimizer works in SQL 2000!

To simplify, I will assume that you have one non-clustered index on
persons.firstname and one on persons.lastname.

If you have a plain query like:

SELECT distinct u.user_id, u.username, u.status_id
FROM statusnames sn, statuses s, users u, users_persons up, persons p,
users_roles ur
WHERE p.lastname like '%'
AND p.firstname like 'a%'
AND s.status_id = u.status_id
AND sn.statusname_id = s.statusname_id
AND u.user_id = up.user_id
AND up.person_id = p.person_id

The optimizer knows that it should use the index on firstname, because
the condition on lastname matches all values in that column (save NULL
values).

If you instead have:

DECLARE @firstname varchar(100)
DECLARE @lastname varchar(100)
SELECT @firstname = 'a%', @lastname = '%'
SELECT distinct u.user_id, u.username, u.status_id
FROM statusnames sn, statuses s, users u, users_persons up, persons p,
users_roles ur
WHERE p.lastname like @lastname
AND p.firstname like @firstname
AND s.status_id = u.status_id
AND sn.statusname_id = s.statusname_id
AND u.user_id = up.user_id
AND up.person_id = p.person_id

The values of @firstname and @lastname are unknown to the optimizer,
since it composes the plan for the whole batch at one go, and it performs
no flow analysis or similar on the commands. In this case, the optimizer
will have to make a choice from some standard assumptions. It could look
at the statistics for the the two name columns and find that one of them
appears to be more selective and decide to use that index. It could also
opt for an index-intersect strategy and seek both indexes. Or, it could
come to the conclusion that none of the index are good enough in the
general case and scan the table instead. When it comes to the last
strategy one should keep in mind that table scan is a good "minimize the
loss" strategy, since all pages are accessed at most once. Using the
index on lastname when @lastname = '%' means that all rows will have to
be access through the index, which means that all pages will be accessed
many times. With 100 rows per page, that 100 times slower than a table scan!

Finally there is this case:

CREATE PROCEDURE some_sp @firstname varchar(100)
@lastname varchar(100) AS
...
EXEC some_sp 'a%', '%'

Here the optimizer performs something known as "parameter sniffing". On
first invocation (or more precisely when there is no plan in the cache),
the optimizer uses the values from the first invocation to build the plan.
The plan remains in the cache. This means that if you first say:

EXEC some_sp 'a%', '%'
EXEC some_sp '%', 'a%'

the first call may execute swiftly, because the optimzer settles for the
index on firstname. But for the second call, the index on firstname is a
complete disaster. Then again, the optimizer may realize this danger and
settle for a table scan in all situations instead.

For a procedure like this, parameter sniffing maybe harmful. One way to
avoid the problem would be to add the WITH RECOMPILE clause to the
CREATE PROCEDURE statement. In this case, the plan is not cached, but
a new plan is created for each call to the procedure.

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

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