Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Try query hints after WHERE=blah,OPTION ( OPTIMIZE FOR (@accountId = 'actual-guid-typed-in-here')) –  Mihai Apr 21 at 9:50
    
I cannot insert MAGIC into code generated by Linq-to-SQL. –  sharptooth Apr 21 at 9:59
    
I'm assuming this is an indexed view. What "type" is AccountId in the underlying table? Is it uniqueidentifier, or varchar/nvarchar? –  David Crowell Apr 21 at 17:13
    
@DavidCrowell: No, the view is not indexed and AccountId is uniqueidentifier. –  sharptooth Apr 22 at 6:03

1 Answer 1

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.

share|improve this answer
    
Note that WITH RECOMPILE will "get around" the parameter sniffing issue, but at the expense of CPU. Not trying to be an alarmist, but keep that in mind. Also, if you are stuck using Linq, and cannot use SPs, Plan Guides would indeed be your best bet. Per @MichaelGreen above. –  Mark Wilkinson Apr 21 at 12:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.