查询优化 - 在 JOIN 上使用条件而不是 WHERE 子句 [英] Query optimization - using conditions on JOIN instead of with WHERE clause

问题描述

在 SP 中，我需要找出第一个帐户的某些客户的 ID，其 Code 与第二个帐户的任何客户相匹配.我写了以下有效的查询 -

Inside an SP I need to find out the Id's of some clients of the first account whose Code matches any of the second account's clients. I wrote the following query that works -

    SELECT DISTINCT cil.Id FROM ClientIdList AS cil
    INNER JOIN Client AS c1
    ON cil.Id = c1.Id
    INNER JOIN Client AS c2
    ON c1.Code = c2.Code
    WHERE c2.AccountId = 2
    ORDER BY cil.Id  

这里的 ClientIdList 是一个单列表类型变量，它保存第一个帐户中所选客户的 ID(在此之前，我需要将此变量用于其他要求).如果我将条件放在 WHERE 子句中作为 JOIN 条件，我可以获得相同的正确输出 -

Here ClientIdList is a single-column table-type variable which holds the Ids of the selected clients from the first account (and I need to use this variable for other requirements prior to this point). I can get the same correct output if I put the condition in the WHERE clause as the JOIN condition as follows -

    SELECT DISTINCT cil.Id FROM ClientIdList AS cil
    INNER JOIN Client AS c1
    ON cil.Id = c1.Id
    INNER JOIN Client AS c2
    ON c1.Code = c2.Code AND c2.AccountId = 2
    ORDER BY cil.Id  

考虑到 2000 个帐户和每个帐户 10000 个客户(即 Client 表中的 2000 x 10000 行)，哪一个是合适的选择?

Considering the 2000 accounts and 10000 clients per account (that is, 2000 x 10000 rows in Client table) which one would be an appropriate choice?

能否进一步优化查询以提高性能?

Can the query be optimized further to improve performance?

实际上条件是c2.AccountId = @accountId，其中@accountId是SP的参数

Edit : Actually the condition is c2.AccountId = @accountId where the @accountId is a parameter to the SP

编辑 2 :据我所知，使用 WHERE 子句版本，将与 Client 表的其余部分执行 JOIN，然后 结果将是根据 WHERE 条件过滤.但是对于更高版本，应该使用满足条件的较小行集来执行 JOIN.我对吗?如果是这样，以后的版本不是应该提供更好的性能吗?

Edit 2 : As much as I understand, with the WHERE clause version the JOIN will be performed with the rest of the Client table, and then the result will be filtered based on the WHERE condition. But with the later version the JOIN should be performed with a smaller set of rows for which the condition satisfies. Am i right? If so, shouldn't the later version give better performance?

推荐答案

无论如何，db 引擎最终执行计划相同，并且没有办法改进此查询的编写(这里不讨论索引).

Whatever, the db engine ends up with the same execution plan and there is no way to improve the writing of this query (not talking about indexes here).

至于最佳实践，我将把 c2.AccountId = 2 放入 WHERE 因为常量(这是有人可能编辑以更改选择的内容)查询，而连接与行为更相关，不应进行此类修改).

As to best practices, I'll put the c2.AccountId = 2 into the WHERE because of the constant (this is something someone might edit to change the selection of the query, whereas the join is more behavior-related and should not be subject to this kind of modification).

但我通常会看到这两篇文章并将其放在 JOIN 中并不会让我感到震惊:-)

But I usually see both of the writings and to put it in the JOIN doesn't shock me that much :-)

这篇关于查询优化 - 在 JOIN 上使用条件而不是 WHERE 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！