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:
The query plan that performs badly is this:
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?