Sql Server Full Text Query Across Multiple Tables - Why So Slow?

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 immediat

Solution 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] ISNOT NULL OR VFT.[KEY] ISNOT NULL

Solution 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')
                )