由于内部和左连接而导致 SQL 查询变慢? [英] Slow SQL Query due to inner and left join?

问题描述

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

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

如果您执行此查询:

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

它会非常非常非常慢.

如果您将该查询更改为使用两个内连接而不是左连接，则速度会非常快.如果你把它改成使用两个左联接而不是一个内联接，它会非常快.

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.

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

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

每当您有一个表变量(或 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?

推荐答案

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

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.

由于两个连接在第一个表上匹配，因此更改顺序不会影响结果的准确性.因此，您可能希望在 LEFT 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:

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.

这篇关于由于内部和左连接而导致 SQL 查询变慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！