<p>Can anyone explain this behavior or how to get around it?</p>

<p>If you execute this query:</p>

<pre><code>select * 
from TblA
left join freetexttable ( TblB, *, 'query' ) on TblA.ID = [Key]
inner join DifferentDbCatalog.dbo.TblC on TblA.ID = TblC.TblAID
</code></pre>

<p>It will be very very very slow.</p>

<p>If you change that query to use two inner joins instead of a left join, it will be very fast.  If you change it to use two left joins instead of an inner join, it will be very fast.</p>

<p>You can observe this same behavior if you use a sql table variable instead of the freetexttable as well.  </p>

<p>The performance problem arises any time you have a table variable (or freetexttable) and a table in a different database catalog where one is in an inner join and the other is in a left join.</p>

<p>Does anyone know why this is slow, or how to speed it up?</p>

[{'answer_id': 47109, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>Index the field you use to perform the join.</p>\n\n<p>A good rule of thumb is to assign an index to any commonly referenced <a href="http://en.wikipedia.org/wiki/Foreign_key" rel="nofollow noreferrer">foreign</a> or <a href="http://en.wikipedia.org/wiki/Candidate_key" rel="nofollow noreferrer">candidate keys</a>.</p>\n'}, {'answer_id': 47120, 'author': 'The How-To Geek', 'author_id': 291, 'author_profile': 'https://Stackoverflow.com/users/291', 'pm_score': 2, 'selected': False, 'text': '<p>What you should usually do is turn on the "Show Actual Execution Plan" option and then take a close look at what is causing the slowdown. (hover your mouse over each join to see the details) You\'ll want to make sure that you are getting an index seek and not a table scan.</p>\n\n<p>I would assume what is happening is that SQL is being forced to pull everything from one table into memory in order to do one of the joins. Sometimes reversing the order that you join the tables will also help things.</p>\n'}, {'answer_id': 47128, 'author': 'Joel Coehoorn', 'author_id': 3043, 'author_profile': 'https://Stackoverflow.com/users/3043', 'pm_score': 3, 'selected': False, 'text': "<p>A general rule of thumb is that OUTER JOINs cause the number of rows in a result set to <em>increase,</em> while INNER JOINs cause the number of rows in a result set to <em>decrease.</em>  Of course, there are plenty of scenarios where the opposite is true as well, but it's more likely to work this way than not.  What you want to do for performance is keep the size of the result set (working set) as small as possible for as long as possible.  </p>\n\n<p>Since both joins match on the first table, changing up the order won't effect the accuracy of the results.  Therefore, you probably want to do the INNER JOIN before the LEFT JOIN:</p>\n\n<pre><code>SELECT * \nFROM TblA\nINNER JOIN DifferentDbCatalog.dbo.TblC on TblA.ID = TblC.TblAID\nLEFT JOIN freetexttable ( TblB, *, 'query' ) on TblA.ID = [Key]\n</code></pre>\n\n<p>As a practical matter, the query optimizer <em>should</em> be smart enough to compile to use the faster option, regardless of which order you specified for the joins. However, it's good practice to pretend that you have a dumb query optimizer, and that query operations happen in order. This helps future maintainers spot potential errors or assumptions about the nature of the tables.</p>\n\n<p>Because the optimizer should re-write things, this probably isn't good enough to fully explain the behavior you're seeing, so you'll still want to <em>examine the execution plan</em> used for each query, and probably add an index as suggested earlier.  This is still a good principle to learn, though.</p>\n"}, {'answer_id': 47131, 'author': 'Eugene Yokota', 'author_id': 3827, 'author_profile': 'https://Stackoverflow.com/users/3827', 'pm_score': 1, 'selected': False, 'text': "<p>Putting <code>freetexttable(TblB, *, 'query')</code> into a temp table may help if it's getting called repeatedly in the execution plan.</p>\n"}]
['https://Stackoverflow.com/questions/47104', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4407/']