SQL Server full text query across multiple tables - why so slow?

Question

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

UPDATE: the plan in in text form:

  |--Nested Loops(Left Semi Join, OUTER REFERENCES:([E].[ID], [V].[ID]))
       |--Hash Match(Inner Join, HASH:([E].[ID])=([O].[EventID]))
       |    |--Hash Match(Inner Join, HASH:([V].[ID])=([E].[VenueID]))
       |    |    |--Clustered Index Scan(OBJECT:([iScene].[dbo].[Venue].[PK_Venue] AS [V]))
       |    |    |--Clustered Index Scan(OBJECT:([iScene].[dbo].[Event].[PK_Event] AS [E]))
       |    |--Clustered Index Scan(OBJECT:([iScene].[dbo].[EventOccurrence].[PK_EventOccurrence] AS [O]))
       |--Concatenation
            |--Table-valued function
            |--Table-valued function

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.

Answer 1

Try rewriting your query using FREETEXTTABLE and see if that helps.

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
    LEFT JOIN FREETEXTTABLE(dbo.Event, Name, 'search') EFT 
        ON E.ID = EFT.[KEY]
    LEFT JOIN FREETEXTTABLE(dbo.Venue, Name, 'search') VFT 
        ON V.ID = VFT.[KEY]
WHERE EFT.[KEY] IS NOT NULL OR VFT.[KEY] IS NOT NULL

Answer 2

How does the execution plan for this compare?

SELECT
    O.ID, O.Name
FROM
    dbo.EventOccurrence O
    WHERE O.EventID IN (
            SELECT
                E.ID
            FROM
                 dbo.Event E
            WHERE
                FREETEXT(E.Name, 'search')
            UNION
            SELECT
                E.ID
            FROM
                 dbo.Event E
                INNER JOIN dbo.Venue V ON E.VenueID = V.ID
            WHERE
                FREETEXT(V.Name, 'search')
                )