Hi. I'm trying to understand the performance of an SQL Server 2008 full-text query I am constructing.
The following query, using a full-text index, returns the correct results immediately:
SELECT
O.ID, O.Name
FROM
dbo.EventOccurrence O
WHERE
FREETEXT(O.Name, 'query')
ie, all EventOccurrences with the word 'query' in their name. And the following query, using a full-text index from a different table, also returns straight away:
SELECT
V.ID, V.Name
FROM
dbo.Venue V
WHERE
FREETEXT(V.Name, 'query')
ie. all Venues with the word 'query' in their name. But if I try to join the tables and do both full-text queries at once, it 12 seconds to return:
SELECT
O.ID, O.Name
FROM
dbo.EventOccurrence O
INNER JOIN dbo.Event E ON O.EventID = E.ID
INNER JOIN dbo.Venue V ON E.VenueID = V.ID
WHERE
FREETEXT(E.Name, 'search')
OR FREETEXT(V.Name, 'search')
Here is the execution plan: http://uploadpad.com/files/query.PNG
From my reading, I didn't think it was even possible to make a free text query across multiple tables in this way, so I'm not sure I am understanding this correctly.
Note that if I remove the WHERE clause from this last query then it returns all results within a second, so it's definitely the full-text that is causing the issue here.
Can someone explain (i) why this is so slow and (ii) if this is even supported / if I am even understanding this correctly.
Thanks in advance for your help.
