Hi Michel,

This indeed is an optimizer issue. The optimizer is not able to do parameter sniffing in the example below. Therefore it might use a non optimal execution plan as the execution plans are cached from one execution to another.

Depending on the selectivity of the parameters that execution plan might not be optimal. If you really need parameterization, you'll have to force a recompile of the execution plan. If you're using SPs, the WITH RECOMPILE option is helpfull.

With SPT, you're either 1. deemed to find a way to force a recompile or 2. to restructure the SQL statement to be less phrone to this and 3. make sure you have the right indexes.

1. Don't use parameterisation for at least one of the parameters or make sure that SQL string is slightly different as SQL server looks at the string and looks up whether that exact same statment has been executed before and uses that executionplan. If SQL server cannot find the exact SQL statement, it will force a new execution plan.

2. Some statements can be better optimized. For example, using ORs often can be written better using unions.

SELECT Client.Numero FROM Client WHERE Client.Name LIKE @Name AND Client.ID=@ID
UNION
SELECT Client.Numero FROM Client WHERE Client.Name2 LIKE @Name AND Client.ID=@ID

3. In the case of the query above, the query would greatly benefit on a compound index on ID, Name, Numero, so SQL server can scan a small range of index nodes and not even touch the table (full index coverage). Smartly choosen compound indexes are a great way to increase performance.

On the whole I don't think the clustered index is going to make a significant case here. Their role is going to be minimized in the future with the rise of SSD harddisk... There is less need to keep freuquently queried records physically together.

Walter,

>For specific queries, when going parameterized, I have some major lack of performance. So, the following could be really long sometimes:
>
>

>DECLARE @Name varchar(20)
>DECLARE @Name2 varchar(20)
>DECLARE @ID integer
>set @Name = 'A301041%'
>set @Name2 = 'A301041%'
>set @ID = 1
>
>SELECT Client.Numero FROM Client WHERE (Client.Name LIKE @Name OR Client.Name2 LIKE @Name2) AND Client.ID=@ID
>

>
>This depends on the value entered. However, if I am not going parameterized, no matter the value, I will always get an optimized search result. But, for certain values, when going parameterized, it can take up to 8 to 50 seconds to find the same query.
>
>This has been discussed before. But, I really need to find what we need to adjust to avoid that.