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.

The following query is proving beyond my abilities to fix - hope someone can help.

SELECT top 100 Id, Name, InsertDate
FROM Foo
WHERE CONTAINS(FooText, '"test*" and "hello*" and "goodbye*"')
ORDER BY InsertDate desc

Without the ORDER BY clause, the query performs fine (< 2 seconds). But when I add the ORDER BY, performance is awful (> 2 minutes).

The problem seems to be an incorrect query plan.

The query plan that performs well is this: enter image description here

The query plan that performs badly is this: enter image description here

IX_Foo_InsertDate is defined as: CREATE CLUSTERED INDEX [IX_Foo_InsertDate] ON [Foo] ( [InsertDate] DESC )

What is causing the change in query plans? How can I fix it?

share|improve this question
    
What is the primary key please? And full table definition with indexes –  gbn May 27 '11 at 7:18
add comment

1 Answer 1

up vote 2 down vote accepted

The optimizer is choosing the different plan because it thinks the benefit of having the pre-sorted InsertDate (using IX_Foo_InsertDate) outweighs the cost once you add the order by clause.

The optimizer cannot be perfect, it might choose a slower path even if statistics are accurate - but it will certainly make poor choices if the statistics are not accurate - are AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS on?

If necessary, you might be able to 'trick' the optimizer like this:

SELECT top 100 Id, Name, InsertDate
FROM Foo
WHERE CONTAINS(FooText, '"test*" and "hello*" and "goodbye*"')
ORDER BY some_function_of(InsertDate) desc

where some_function_of(InsertDate) has the same order as InsertDate

share|improve this answer
    
That was exactly right - thanks! –  cbp May 30 '11 at 2:12
add comment

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.