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.
每当有一个表变量(或*文本表)和一个表在不同的数据库目录中时,就会出现性能问题,其中一个表位于内连接中,另一个表位于左连接中。
Does anyone know why this is slow, or how to speed it up?
有谁知道为什么这么慢,或者如何加快速度?
4 个解决方案
#1
7
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 should 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.
实际上,无论您为连接指定了哪个顺序,查询优化器都应足够智能,以便编译以使用更快的选项。但是,最好假装您有一个愚蠢的查询优化器,并且查询操作按顺序发生。这有助于未来的维护者发现有关表格性质的潜在错误或假设。
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 plan used for each query, and probably add an index as suggested earlier. This is still a good principle to learn, though.
因为优化器应该重写东西,这可能不足以完全解释你所看到的行为,所以你仍然想要检查用于每个查询的执行计划,并且可能添加一个索引,如前面所建议的。不过,这仍然是一个很好的学习原则。
#2
3
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*将所有内容从一个表拉到内存中以便进行其中一个连接。有时逆转加入表格的顺序也会有所帮助。
#3
0
Index the field you use to perform the join.
索引用于执行连接的字段。
A good rule of thumb is to assign an index to any commonly referenced foreign or candidate keys.
一个好的经验法则是为任何通常引用的外键或候选键分配索引。
#4
0
Putting freetexttable(TblB, *, 'query') into a temp table may help if it's getting called repeatedly in the execution plan.
将freetexttable(TblB,*,'query')放入临时表可能会有所帮助,如果它在执行计划中被重复调用。
#1
7
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 should 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.
实际上,无论您为连接指定了哪个顺序,查询优化器都应足够智能,以便编译以使用更快的选项。但是,最好假装您有一个愚蠢的查询优化器,并且查询操作按顺序发生。这有助于未来的维护者发现有关表格性质的潜在错误或假设。
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 plan used for each query, and probably add an index as suggested earlier. This is still a good principle to learn, though.
因为优化器应该重写东西,这可能不足以完全解释你所看到的行为,所以你仍然想要检查用于每个查询的执行计划,并且可能添加一个索引,如前面所建议的。不过,这仍然是一个很好的学习原则。
#2
3
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*将所有内容从一个表拉到内存中以便进行其中一个连接。有时逆转加入表格的顺序也会有所帮助。
#3
0
Index the field you use to perform the join.
索引用于执行连接的字段。
A good rule of thumb is to assign an index to any commonly referenced foreign or candidate keys.
一个好的经验法则是为任何通常引用的外键或候选键分配索引。
#4
0
Putting freetexttable(TblB, *, 'query') into a temp table may help if it's getting called repeatedly in the execution plan.
将freetexttable(TblB,*,'query')放入临时表可能会有所帮助,如果它在执行计划中被重复调用。