• SQL Server 中Inner join 和where的效率差异


    SQL Server 中Inner join 和where的效率差异

    总结出来时说:对小数据量(<N万)的来说效率几乎无差异,更有说法说Inner join 和Where只是SQL标准不同,在查询分析器中SQL Server查询分析器是将Where直接转换为Join后查询的。

    如是有了如下比较结果(均在查询分析器中查询和计时):

    语句(1)

    declare @operatorName nvarchar(50)

    set @operatorName = '%'

    select distinct item.* from item , customer_item , customer_operator ,operator

    where item.itemcode = customer_item.itemCode

    and customer_item.customerCode = customer_operator.customerCode

    and customer_operator.operatorId = customer_operator.operatorId

    and operator.operatorName like @operatorName

    and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0

    查询结果,74行,共时间0:00:04

    语句(2)

    declare @operatorName nvarchar(50)

    set @operatorName = '%'

    select distinct item.* from item inner join customer_item

    on item.itemcode = customer_item.itemCode

    inner join customer_operator on customer_item.customerCode = customer_operator.customerCode

    inner join operator on customer_operator.operatorId = operator.operatorId

    where operator.operatorName like @operatorName

    and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0

    共74行,时间0:00:01

    后检查发现语句(1)中有一个重复自查询条件 :customer_operator.operatorId = customer_operator.operatorId

    将其叶加到语句2中,语句(3)

    declare @operatorName nvarchar(50)

    set @operatorName = '%'

    select distinct item.* from item inner join customer_item

    on item.itemcode = customer_item.itemCode

    inner join customer_operator on customer_item.customerCode = customer_operator.customerCode

    inner join operator on customer_operator.operatorId = operator.operatorId

    where operator.operatorName like @operatorName

    and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0

    and customer_operator.operatorId = customer_operator.operatorId

    所用时间和结果都为74行,时间0:00:01。

    将语句(1)中的去掉该条件后成为语句(4)

    declare @operatorName nvarchar(50)

    set @operatorName = '%'

    select distinct item.* from item , customer_item , customer_operator ,operator

    where item.itemcode = customer_item.itemCode

    and customer_item.customerCode = customer_operator.customerCode

    --and customer_operator.operatorId = customer_operator.operatorId

    and operator.operatorName like @operatorName

    and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0

    时间和结果为74行,时间0:00:01。

    终于发现了些他们的差异。

    结论:

    尽量使用Join 而不是Where来列出关联条件,特别是多个表联合的时候。

    原因是:

    (1)在效率上,Where可能具有和Inner join一样的效率。但基本可以肯定的(通过SQLServer帮助和其它资料,以及本测试)是Join的效率不比Where差。

    (2)使用Join可以帮助检查语句中的无效或者误写的关联条件


  • 相关阅读:
    mysql的备份与恢复(windows、Linux并拷贝至备机)
    eclipse导出可执行jar
    ORCLE中两张表对比更新合入(MERGE INTO)
    js中事件冒泡的问题
    Spring事务传播行为详解
    Java中的锁分类与使用
    用某浏览器全屏延时启动应用
    Springboot整合WebSocket的交互实例(点对点、点对面)
    Windows程序设计------字体不等宽引出的问题及其细节知识
    关于VS2013使用constexpr报错问题
  • 原文地址:https://www.cnblogs.com/jankerxp/p/7774031.html
Copyright © 2020-2023  润新知