July 25, 2011 at 3:46 pm
Slightly confused with execution plan for very simple query that calls table with FTS enabled-
select * from tblPosts where id= @id1 and contains (posttext, ‘test’)
Without “contains” predicate query uses clustered index seek, but with “contains’ it uses first of all Remote Scan, i.e. FTS selects all records with “test” keyword and only after that index applied.
That means for big tables (with more than 10 M records- my case) and general keywords to search query execution time can be pretty big as index will come to scene only after FTS search completed.
Am I missing something or this is because 2005 FTS is part of OS?
Is 2008 somehow different on this matter?
Any thoughts really appreciated.
July 26, 2011 at 1:44 pm
OK, this is what I found (may be it can help somebody else with same issue):
from this article http://msdn.microsoft.com/en-us/library/cc917695.aspx#Full-text_Configuration
I got that unfortunately my assumption was correct: "As a result, even though your query may have additional conditions that significantly reduce the final number of rows returned by the query, the MSFTESQL service still returns all the matching keys for the target search."
As far as 2008 version- after playing a bit with Express edition it seems it behaves differently (smarter :-))- another good article http://technet.microsoft.com/en-us/library/cc721269(SQL.100).aspx
Good luck, Yuri
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply