performance 由于内部联接和左联接导致 SQL 查询变慢？

Slow SQL Query due to inner and left join?

提问by Kevin Berridge

Can anyone explain this behavior or how to get around it?

谁能解释这种行为或如何解决它？

If you execute this query:

如果您执行此查询：

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

It will be very very very slow.

它会非常非常非常缓慢。

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.

如果您将该查询更改为使用两个内连接而不是左连接，则速度会非常快。如果将其更改为使用两个左联接而不是内联接，则速度会非常快。

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

如果您也使用 sql 表变量而不是 freetexttable，您可以观察到相同的行为。

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.

每当您有一个表变量（或 freetexttable）和一个位于不同数据库目录中的表（其中一个处于内部联接中，另一个处于左联接中）时，就会出现性能问题。

Does anyone know why this is slow, or how to speed it up?

有谁知道为什么这很慢，或者如何加快速度？

回答by Joel Coehoorn

A general rule of thumb is that OUTER JOINs cause the number of rows in a result set to increase,while INNER JOINs cause the number of rows in a result set to decrease.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.

一般的经验法则是 OUTER JOIN 导致结果集中的行数增加，而 INNER JOIN 导致结果集中的行数减少。当然，在很多情况下也有相反的情况，但这种方式更有可能奏效。您想要为性能做的是尽可能长时间地保持结果集（工作集）的大小尽可能小。

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:

由于两个连接在第一个表上匹配，因此更改顺序不会影响结果的准确性。因此，您可能希望在 LEFT JOIN 之前执行 INNER JOIN：

SELECT * 
FROM TblA
INNER JOIN DifferentDbCatalog.dbo.TblC on TblA.ID = TblC.TblAID
LEFT JOIN freetexttable ( TblB, *, 'query' ) on TblA.ID = [Key]

As a practical matter, the query optimizer shouldbe 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.

实际上，查询优化器应该足够聪明，可以编译使用更快的选项，而不管您为连接指定的顺序如何。但是，最好假设您有一个愚蠢的查询优化器，并且查询操作按顺序发生。这有助于未来的维护者发现有关表性质的潜在错误或假设。

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 examine the execution planused for each query, and probably add an index as suggested earlier. This is still a good principle to learn, though.

因为优化器应该重写一些东西，这可能不足以完全解释你所看到的行为，所以你仍然需要检查用于每个查询的执行计划，并可能像之前建议的那样添加索引. 不过，这仍然是一个很好的学习原则。

回答by The How-To Geek

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.

您通常应该做的是打开“显示实际执行计划”选项，然后仔细查看导致速度变慢的原因。（将鼠标悬停在每个连接上以查看详细信息）您需要确保获得的是索引查找而不是表扫描。

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.

我认为正在发生的事情是 SQL 被迫将所有内容从一张表中提取到内存中，以便进行其中一个连接。有时颠倒加入表格的顺序也会有所帮助。

回答by The How-To Geek

Index the field you use to perform the join.

索引用于执行联接的字段。

A good rule of thumb is to assign an index to any commonly referenced foreignor candidate keys.

一个好的经验法则是为任何经常引用的外键或候选键分配一个索引。

回答by Eugene Yokota

Putting freetexttable(TblB, *, 'query')into a temp table may help if it's getting called repeatedly in the execution plan.

把freetexttable(TblB, *, 'query')到一个临时表可以帮助，如果它得到执行计划一再呼吁。