>Has anyone found a way to speed up SQL Server when using the LIKE operator with a % character? When doing it from the interface, this is as it is in VFP. But, when using it from our application, where everything is parameterized, this is slow. I have been answering some requests in the past weeks in regards to that where everyone is wondering why it is so slow when searching for field that starts with something. They got used to VFP where this is instant result. What can I do to speed that up?

Michel,

I don't know much about sqlserver (yet), but have you tried option 1 here

1. "Disable" parameter sniffing by using local DECLARE'd variables that
you SET equal to the parameters inside the stored proc, and use the local
variables instead of the offending parameters in the queries. This is the
solution that you found yourself. SQL can't use parameter sniffing in
this case so it must make some guesses, but in this case the guess based
on average column density is better than the plan based on a specific but
"wrong" parameter value (NULL).

http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/2c5c8472bff7dea0/ad37d8aec76e2b8f?lnk=st&q=#ad37d8aec76e2b8f