We have a slow-performing query and, after some analysis, have narrowed the cause down to using parametrized queries in conjunction with LIKE and aggregate comparisons used in the WHERE clause. If we use a non-parametrized query (but keep the WHERE
clause the same) the query performs much faster and, in fact, executes a different plan. Using OPTIMIZE FOR UNKNOWN is not an option for us as we are using SQL Server 2005. Obviously, we'd like to use parametrized queries to prevent SQL injection
but the slow perf is unacceptable. We need the LIKE to support wildcard scenarios. If we need to we'll use dynamic SQL (scrubbed as much as we can) in lieu of parametrized queries. We're wondering, however, is there another option that would
give us the perf without sacrificing safety? I can provide a sample database, query, plans, etc. if necessary...I'm assuming SQL Server generates a less efficient plan with the parametrized query as it can't make any assumptions about the parameter values...
View Complete Post