How do I deal with query getting slower when a select parameter is not specified inline?

I faced this problem twice with SQL Azure and two different queries so far.

Something like this:

select top(100) * from SomeView WHERE AccountId='actual-guid-typed-in-here'

runs super fast but equivalent code (generated from Linq-to-SQL)

declare @accountId uniqueidentifier;
set @accountId = 'actual-guid-typed-in-here'
select top(100) * from SomeView WHERE AccountId=@accountId

runs super slow and upon careful analysis I see that the engine uses the wrong index in the latter case. Note that the latter case would also arise if I moved my code into a stored procedure.

The two queries are identical and so I expect them to be executed identically but the database engine thinks otherwise.

What's the typical solution from this case?

If you look at the two execution plans you will see that the estimated cardinality is different in each case. When the actual value is in the SELECT statement the optimiser can use the cardinality of that one, actual value to choose an access path. When the SELECT has a variable the optimiser cannot know whether you have provided a very popular or very unpopular accountId so produces a 'best guess' plan. Unfortunately for your situation this is awful.

Stored procedures (SPs) would be good. Then you can put all the query hints you want in there. Also use WITH RECOMPILE to get around parameter sniffing issues. If you have good inexes already, and you don't have access to the source SQL or can't use SPs, Plan Guides may be a good option. If you end up using them be sure to schedule a review every so often to ensure the chosen plan is still optimal.

StackExchange Database Administrators Q#63560, answer score: 2