SQL Server 2005-内部联接顺序 [英] SQL Server 2005 - Order of Inner Joins

问题描述

我有一个查询，其中Where子句中包含三个内部join语句.该查询大约需要2分钟才能执行.如果仅更改两个内部联接的顺序，性能就会下降到40秒.

I have a query containing three inner join statements in the Where clause. The query takes roughly 2 minutes to execute. If I simply change the order of two of the inner joins, performance drops to 40 seconds.

除了更改内部联接的顺序外，什么都不做会对查询性能产生如此大的影响?我本以为优化器会解决所有这些问题.

How can doing nothing but changing the order of the inner joins have such a drastic impact of query performance? I would have thought the optimizer would figure all this out.

推荐答案

SQL是声明性的，也就是说，JOIN顺序无关紧要.

SQL is declarative, that is, the JOIN order should not matter.

但是实际上，如果优化器没有探索所有选项(理论上可能要花费数月)，这是一个复杂的查询.

However it can in practice, say, if it's a complex query when the optimiser does not explore all options (which in theory could take months).

另一种选择是，如果您重新排序并获得不同的结果，这将是一个非常不同的查询，但这通常与OUTER JOIN一起使用.

Another option is that it's a very different query if you reorder and you get different results, but this is usually with OUTER JOINs.

这也可能是指定ON子句的方式如果您对FROM子句重新排序，则必须更改.除非您使用的是较旧(且较差)的JOIN-in-the-WHERE条款.

And it could also be the way the ON clause is specified It has to change if you reorder the FROM clause. Unless you are using the older (and bad) JOIN-in-the-WHERE-clause.

最后，如果有问题，您可以使用括号来更改评估顺序以使您的意图更明确，例如，首先对大表进行过滤以生成派生表.

Finally, if it's a concern you could use parenthesis to change evaluation order to make your intentions clear, say, filter on a large table first to generate a derived table.

这篇关于SQL Server 2005-内部联接顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！