• in和exists哪个效率高本人测试证明


     in和exists哪个效率高本人测试证明

    SQLSERVR语句 in和exists哪个效率高自己测试本人测试证明

    最近很多人讨论in和exists哪个效率高,今天就自己测试一下

    我使用的是客户的数据库GPOSDB(已经有数据)

    环境:SQLSERVER2005   Windows7

    我的测试条件:两个表作连接根据VC_IC_CardNO字段,查出CT_InhouseCard表中的VC_IC_CardNO(卡号)在CT_FuelingData表中存在的记录

    前提:某些人可能在SQL语句中有多个in,或者多个exists,这些情况很难测试效率的,因为大家的条件都不相同

    例如下面两个SQL语句

    复制代码
    1 SELECT  OrderNo, SiteCode, AreaCode
    2 FROM    SchedulingProgram
    3 WHERE   AreaCode IN ( 'P', 'M' ) AND SiteCode IN ( SELECT   SiteCode
    4                                                    FROM     EnvBasicInfo
    5                                                    WHERE    cityiD = 31 ) AND OrderNo NOT IN (
    6         SELECT  OrderNo
    7         FROM    KK_DeliveryinfoTmp )
    复制代码

    上面SQL语句IN里面有IN和NOT IN

    复制代码
    1 SELECT  OrderNo, SiteCode, AreaCode
    2 FROM    SchedulingProgram
    3 WHERE   ( AreaCode IN ( 'P', 'M' ) AND SiteCode IN ( SELECT SiteCode
    4                                                      FROM   EnvBasicInfo
    5                                                      WHERE  cityiD = 31 )
    6         ) AND NOT EXISTS ( SELECT   OrderNo
    7                            FROM     KK_DeliveryinfoTmp
    8                            WHERE    KK_DeliveryinfoTmp.OrderNo = SchedulingProgram.OrderNo )
    复制代码

    上面的SQL语句IN里面又有NOT EXISTS

    这样的情况很难测试同等条件下IN语句和EXISTS语句的效率

    还有一个非SARG运算符

    在《SQLSERVER企业级平台管理实践》的第424页里提到:

    SQLSERVER对筛选条件(search argument/SARG)的写法有一定的建议

    对于不使用SARG运算符的表达式,索引是没有用的,SQLSERVER对它们很难使用比较优化的做法。非SARG运算符包括

    NOT、<>、NOT EXISTS、NOT IN、NOT LIKE和内部函数,例如:Convert、Upper等

    所以当您的表中有索引并且SQL语句包含非SARG运算符,那么当测试SQL语句的执行时间的时候肯定相差很大,

    因为有些SQL语句走索引,有些SQL语句不走索引


    建表脚本

    注意:两个表中都有索引!!

    CT_FuelingData表

    复制代码
     1 USE [GPOSDB]
     2 GO
     3 /****** 对象:  Table [dbo].[CT_FuelingData]    脚本日期: 08/24/2013 11:00:34 ******/
     4 SET ANSI_NULLS ON
     5 GO
     6 SET QUOTED_IDENTIFIER ON
     7 GO
     8 SET ANSI_PADDING ON
     9 GO
    10 CREATE TABLE [dbo].[CT_FuelingData](
    11     [RecordNO] [int] IDENTITY(1,1) NOT NULL,
    12     [I_FD_StationNo] [int] NOT NULL,
    13     [VC_FD_No] [varchar](50) NOT NULL,
    14     [VC_FD_Cardno] [varchar](50) NOT NULL,
    15     [I_FD_CardStatus] [int] NULL,
    16     [LI_FD_CTC] [bigint] NOT NULL,
    17     [I_FD_TypeCode] [int] NULL,
    18     [I_FD_PumpID] [int] NOT NULL,
    19     [VC_FD_OilType] [varchar](50) NULL,
    20     [DE_FD_Volume] [decimal](18, 2) NULL,
    21     [DE_FD_Price] [decimal](18, 2) NULL,
    22     [DE_FD_Amount] [decimal](18, 2) NULL,
    23     [I_FD_Point] [decimal](10, 2) NULL,
    24     [D_FD_DateTime] [datetime] NOT NULL,
    25     [VC_FD_GroupNo] [varchar](50) NULL,
    26     [D_FD_GroupDate] [datetime] NULL,
    27     [DE_FD_CardAmount] [decimal](18, 2) NULL,
    28     [DE_FD_VolumeTotals] [decimal](18, 2) NULL,
    29     [DE_FD_AmountTotals] [decimal](18, 2) NULL,
    30     [I_FD_ISSend] [int] NULL,
    31     [VC_FD_CardMoneyauthFile] [varchar](50) NULL,
    32     [D_Month] [datetime] NULL,
    33  CONSTRAINT [PK_CT_FuelingData_1] PRIMARY KEY CLUSTERED 
    34 (
    35     [VC_FD_No] ASC
    36 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    37 ) ON [PRIMARY]
    38 
    39 GO
    40 SET ANSI_PADDING OFF
    复制代码

    CT_InhouseCard表

    复制代码
     1 USE [GPOSDB]
     2 GO
     3 /****** 对象:  Table [dbo].[CT_InhouseCard]    脚本日期: 08/24/2013 10:59:58 ******/
     4 SET ANSI_NULLS ON
     5 GO
     6 SET QUOTED_IDENTIFIER ON
     7 GO
     8 SET ANSI_PADDING ON
     9 GO
    10 CREATE TABLE [dbo].[CT_InhouseCard](
    11     [RecordNO] [int] IDENTITY(1,1) NOT NULL,
    12     [VC_IC_CardNO] [varchar](50) NOT NULL,
    13     [VC_IC_PhysicalNO] [varchar](50) NULL,
    14     [I_IC_CardType] [int] NULL,
    15     [VC_IC_UserName] [varchar](50) NULL,
    16     [VC_IC_JobNO] [varchar](50) NULL,
    17     [VC_IC_UserID] [varchar](50) NULL,
    18     [VC_IC_Password] [varchar](50) NULL,
    19     [DE_IC_CardAmount] [decimal](18, 2) NULL,
    20     [DE_IC_AppendAmount] [decimal](18, 2) NULL,
    21     [DE_IC_ConsumerAmount] [decimal](18, 2) NULL,
    22     [I_IC_ISLost] [int] NULL,
    23     [D_IC_UsedDateTime] [datetime] NULL,
    24     [D_IC_UselifeDateTime] [datetime] NULL,
    25     [I_IC_IssueStationNO] [int] NULL,
    26     [VC_IC_IssuerNO] [varchar](50) NULL,
    27     [D_IC_IssueDateTime] [datetime] NULL,
    28     [D_IC_LastUpdateDateTime] [datetime] NULL,
    29     [I_IC_CardStatus] [int] NULL,
    30     [VC_IC_Remark] [varchar](256) NULL,
    31  CONSTRAINT [PK_CT_InhouseCard] PRIMARY KEY CLUSTERED 
    32 (
    33     [VC_IC_CardNO] ASC
    34 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    35 ) ON [PRIMARY]
    36 
    37 GO
    38 SET ANSI_PADDING OFF
    复制代码

    测试脚本

    因为这个是客户的数据库,本来里面已经有数据了,所以在测试之前先更新两个表的统计信息,以做到公正

    复制代码
    1 USE [GPOSDB]
    2 GO
    3 UPDATE STATISTICS CT_FuelingData
    4 UPDATE STATISTICS CT_InhouseCard
    5 GO
    复制代码

    IN语句

    复制代码
     1 USE [GPOSDB]
     2 GO
     3 DBCC DROPCLEANBUFFERS
     4 GO
     5 DBCC FREEPROCCACHE
     6 GO
     7 SET STATISTICS IO ON
     8 GO
     9 SET STATISTICS TIME ON
    10 GO
    11 SET STATISTICS PROFILE ON
    12 GO
    13 SELECT * FROM [dbo].[CT_FuelingData] WHERE [VC_FD_Cardno] IN (SELECT [VC_IC_CardNO] FROM [dbo].[CT_InhouseCard])
    复制代码

    EXISTS语句

    复制代码
     1 USE [GPOSDB]
     2 GO
     3 DBCC DROPCLEANBUFFERS
     4 GO
     5 DBCC FREEPROCCACHE
     6 GO
     7 SET STATISTICS IO ON
     8 GO
     9 SET STATISTICS TIME ON
    10 GO
    11 SET STATISTICS PROFILE ON
    12 GO
    13 SELECT  *
    14 FROM    [dbo].[CT_FuelingData]
    15 WHERE   EXISTS ( SELECT [VC_IC_CardNO]
    16                  FROM   [dbo].[CT_InhouseCard]
    17                  WHERE  [dbo].[CT_FuelingData].[VC_FD_Cardno] = [dbo].[CT_InhouseCard].[VC_IC_CardNO] )
    复制代码

    测试结果

    IN语句

    复制代码
     1 SQL Server 执行时间:
     2    CPU 时间 = 0 毫秒,占用时间 = 2 毫秒。
     3 SQL Server 分析和编译时间: 
     4    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
     5 
     6 SQL Server 执行时间:
     7    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
     8 SQL Server 分析和编译时间: 
     9    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
    10 
    11 SQL Server 执行时间:
    12    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
    13 SQL Server 分析和编译时间: 
    14    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
    15 
    16 SQL Server 执行时间:
    17    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
    18 SQL Server 分析和编译时间: 
    19    CPU 时间 = 31 毫秒,占用时间 = 67 毫秒。
    20 
    21 (167 行受影响)
    22'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
    23'CT_FuelingData'。扫描计数 1,逻辑读取 31 次,物理读取 1 次,预读 64 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
    24'CT_InhouseCard'。扫描计数 1,逻辑读取 2 次,物理读取 1 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
    25 
    26 (4 行受影响)
    27 
    28 SQL Server 执行时间:
    29    CPU 时间 = 16 毫秒,占用时间 = 192 毫秒。
    复制代码

    EXISTS语句

    复制代码
     1 SQL Server 分析和编译时间: 
     2    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
     3 
     4 SQL Server 执行时间:
     5    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
     6 SQL Server 分析和编译时间: 
     7    CPU 时间 = 0 毫秒,占用时间 = 34 毫秒。
     8 
     9 (167 行受影响)
    10'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
    11'CT_FuelingData'。扫描计数 1,逻辑读取 31 次,物理读取 1 次,预读 64 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
    12'CT_InhouseCard'。扫描计数 1,逻辑读取 2 次,物理读取 1 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
    13 
    14 (4 行受影响)
    15 
    16 SQL Server 执行时间:
    17    CPU 时间 = 0 毫秒,占用时间 = 163 毫秒。
    复制代码

    大家可以看到除了执行时间有一点差别,IO是一样的

    因为数据量比较大,所以两个查询都用到了Worktable(中间表)来存储中间结果

    IN语句的执行计划

    EXISTS语句的执行计划

    从执行计划可以看到两个SQL语句的开销都是一样的,而且大家都使用了右半连接(Right Semi Join)

    至于什么是半连接(Semi-join)大家可以看一下这篇文章:SQL Join的一些总结

     

    总结

    从上面实际的执行来比较,,IN语句和EXISTS语句基本上都是一样的效率

    如有不对的地方,欢迎大家来拍砖o(∩_∩)o

  • 相关阅读:
    【CSS】CSS 页面布局:盒子模型(内容区、边框、内边距、外边距)
    压缩/解压文件
    WebApiClient.JIT
    并行+异步执行操作
    JSON输出时不输出某些属性值
    ASP.NET MVC中Filter过滤器的使用
    使用git克隆github上的项目失败,报错error: RPC failed; curl 56 OpenSSL SSL_read: SSL_ERROR_SYSCALL, errno 10054
    C# 实现WebSocket通信
    写日志
    list随机生成数值
  • 原文地址:https://www.cnblogs.com/Leo_wl/p/3280235.html
Copyright © 2020-2023  润新知