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 am seeing some strange behaviour with the following T-SQL query in SQL Server 2012:

select Id 
FROM dbo.Person 
WHERE CONTAINS(Name, '"John" AND "Smith"')
order by Name

Executing this query alone gives me about 1'300 results in less than two seconds (there's a fulltext index on Name)

However, when I change the query to this:

select Id 
FROM dbo.Person 
WHERE CONTAINS(Name, '"John" AND "Smith"')
order by Name
offset 0 rows
fetch next 10 ROWS ONLY

It takes more than 20 seconds to give me 10 results.

The following query is even worse:

SELECT Id 
FROM ( 
    SELECT ROW_NUMBER() OVER (ORDER BY Name) AS RowNum, Id 
    FROM dbo.Person
    WHERE CONTAINS(Name, '"John" AND "Smith"') ) AS RowConstrainedResult 
WHERE RowNum >= 0 AND RowNum < 11 
ORDER BY RowNum

It takes more than 1.5 minutes to complete!

Any ideas?

Slow plan

Slow

Fast plan

Fast

share|improve this question
    
What happens if you change your second query to SELECT TOP 10 * .... ORDER BY Name? –  Lamak Jul 4 '12 at 18:57
    
On what columns is index IX_PersonSearch... made? You're getting a key lookup because you're selecting * from the table and the used index does not contain all the output columns. I think you should select only the columns you need and then include them in the non-clustered index as included columns, not index columns. –  marceln Jul 4 '12 at 18:58
    
Can you post you indexes on the table (create script)? –  Peter Kiss Jul 4 '12 at 18:59
3  
The ID is always included in every nonclustered index. This is the way SQL Server is able to to key lookups (by ID). –  usr Jul 4 '12 at 19:07
1  
What I forgot to mention: When i do the same query with LIKE instead of CONTAINS, is fast, too. (Paginated or not) –  vrs Jul 4 '12 at 19:13
show 10 more comments

migrated from stackoverflow.com Jul 10 '12 at 16:25

This question came from our site for professional and enthusiast programmers.

3 Answers

This looks like classic selectivity misestimation. Not sure what can be done about it as the "driver" of the query is fulltext search which you cannot augment with statistics.

Try rewriting the where contains predicate to an inner join containstable (CONTAINSTABLE) and apply join order hints to force the shape of the plan.

That is not a perfect solution because it has maintenance issues, but I cannot see another way.

share|improve this answer
    
Thanks for your answer, I tried it. Same result though: When not using pagination, the query is really fast. When Paginating it suddenly becomes really slow again :/ –  vrs Jul 4 '12 at 20:46
    
Ok can you post the plan as an image and your query? My guess it that we didn't yet succeed to generate the desired shape. –  usr Jul 4 '12 at 20:53
add comment

As you just want the TOP 10 ordered by name it thinks it will be quicker to work down the index on name in order and look to see if each row matches the CONTAINS(Name, '"John" AND "Smith"') ) predicate.

Presumably it takes many more rows to find the 10 matches required then it expects and this cardinality issue is compounded by the number of key lookups.

A quick hack to stop it using this plan would be to change the ORDER BY to ORDER BY Name + '' although using CONTAINSTABLE in conjunction with FORCE ORDER should also work.

share|improve this answer
add comment
up vote 2 down vote accepted

I managed to solve the problem:

As I said in the question there were indizes on all columns + statistics for each column. (Because of legacy LIKE queries) I removed all indizes and statistics, added the fulltext search and voilà, the query became really fast.

It seems the indizes led to a different Execution Plan.

Thank you all very much for your help!

share|improve this answer
    
Please accept your answer so we know you solved the problem. Thanks! –  Jon Seigel Jul 10 '12 at 17:03
1  
Well deleting the index entirely is one way of preventing it from being used I guess! –  Martin Smith Jul 10 '12 at 17:13
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.