SQLSERVER语句 in和exists哪个效率高本人测试证明
SQLSERVR语句 in和exists哪个效率高本人测试证明 最近很多人讨论in和exists哪个效率高,今天就自己测试一下 我使用的是客户的数据库GPOSDB(已经有数据) 环境 :SQLSERVER2005 Windows7 我的测试条件 :两个表作连接根据VC_IC_CardNO字段,查出CT_InhouseCard
SQLSERVR语句 in和exists哪个效率高本人测试证明
最近很多人讨论in和exists哪个效率高,今天就自己测试一下
我使用的是客户的数据库GPOSDB(已经有数据)
环境:SQLSERVER2005 Windows7
我的测试条件:两个表作连接根据VC_IC_CardNO字段,查出CT_InhouseCard表中的VC_IC_CardNO(卡号)在CT_FuelingData表中存在的记录
前提:某些人可能在SQL语句中有多个in,或者多个exists,这些情况很难测试效率的,因为大家的条件都不相同
例如下面两个SQL语句
<span>1</span> <span>SELECT</span><span> OrderNo, SiteCode, AreaCode </span><span>2</span> <span>FROM</span><span> SchedulingProgram </span><span>3</span> <span>WHERE</span> AreaCode <span>IN</span> ( <span>'</span><span>P</span><span>'</span>, <span>'</span><span>M</span><span>'</span> ) <span>AND</span> SiteCode <span>IN</span> ( <span>SELECT</span><span> SiteCode </span><span>4</span> <span>FROM</span><span> EnvBasicInfo </span><span>5</span> <span>WHERE</span> cityiD <span>=</span> <span>31</span> ) <span>AND</span> OrderNo <span>NOT</span> <span>IN</span><span> ( </span><span>6</span> <span>SELECT</span><span> OrderNo </span><span>7</span> <span>FROM</span> KK_DeliveryinfoTmp )
上面SQL语句IN里面有IN和NOT IN
<span>1</span> <span>SELECT</span><span> OrderNo, SiteCode, AreaCode </span><span>2</span> <span>FROM</span><span> SchedulingProgram </span><span>3</span> <span>WHERE</span> ( AreaCode <span>IN</span> ( <span>'</span><span>P</span><span>'</span>, <span>'</span><span>M</span><span>'</span> ) <span>AND</span> SiteCode <span>IN</span> ( <span>SELECT</span><span> SiteCode </span><span>4</span> <span>FROM</span><span> EnvBasicInfo </span><span>5</span> <span>WHERE</span> cityiD <span>=</span> <span>31</span><span> ) </span><span>6</span> ) <span>AND</span> <span>NOT</span> <span>EXISTS</span> ( <span>SELECT</span><span> OrderNo </span><span>7</span> <span>FROM</span><span> KK_DeliveryinfoTmp </span><span>8</span> <span>WHERE</span> KK_DeliveryinfoTmp.OrderNo <span>=</span> 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表
<span> 1</span> <span>USE</span> <span>[</span><span>GPOSDB</span><span>]</span> <span> 2</span> <span>GO</span> <span> 3</span> <span>/*</span><span>***** 对象: Table [dbo].[CT_FuelingData] 脚本日期: 08/24/2013 11:00:34 *****</span><span>*/</span> <span> 4</span> <span>SET</span> ANSI_NULLS <span>ON</span> <span> 5</span> <span>GO</span> <span> 6</span> <span>SET</span> QUOTED_IDENTIFIER <span>ON</span> <span> 7</span> <span>GO</span> <span> 8</span> <span>SET</span> ANSI_PADDING <span>ON</span> <span> 9</span> <span>GO</span> <span>10</span> <span>CREATE</span> <span>TABLE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>CT_FuelingData</span><span>]</span><span>( </span><span>11</span> <span>[</span><span>RecordNO</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>NOT</span> <span>NULL</span><span>, </span><span>12</span> <span>[</span><span>I_FD_StationNo</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NOT</span> <span>NULL</span><span>, </span><span>13</span> <span>[</span><span>VC_FD_No</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NOT</span> <span>NULL</span><span>, </span><span>14</span> <span>[</span><span>VC_FD_Cardno</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NOT</span> <span>NULL</span><span>, </span><span>15</span> <span>[</span><span>I_FD_CardStatus</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NULL</span><span>, </span><span>16</span> <span>[</span><span>LI_FD_CTC</span><span>]</span> <span>[</span><span>bigint</span><span>]</span> <span>NOT</span> <span>NULL</span><span>, </span><span>17</span> <span>[</span><span>I_FD_TypeCode</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NULL</span><span>, </span><span>18</span> <span>[</span><span>I_FD_PumpID</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NOT</span> <span>NULL</span><span>, </span><span>19</span> <span>[</span><span>VC_FD_OilType</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NULL</span><span>, </span><span>20</span> <span>[</span><span>DE_FD_Volume</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>18</span>, <span>2</span>) <span>NULL</span><span>, </span><span>21</span> <span>[</span><span>DE_FD_Price</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>18</span>, <span>2</span>) <span>NULL</span><span>, </span><span>22</span> <span>[</span><span>DE_FD_Amount</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>18</span>, <span>2</span>) <span>NULL</span><span>, </span><span>23</span> <span>[</span><span>I_FD_Point</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>10</span>, <span>2</span>) <span>NULL</span><span>, </span><span>24</span> <span>[</span><span>D_FD_DateTime</span><span>]</span> <span>[</span><span>datetime</span><span>]</span> <span>NOT</span> <span>NULL</span><span>, </span><span>25</span> <span>[</span><span>VC_FD_GroupNo</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NULL</span><span>, </span><span>26</span> <span>[</span><span>D_FD_GroupDate</span><span>]</span> <span>[</span><span>datetime</span><span>]</span> <span>NULL</span><span>, </span><span>27</span> <span>[</span><span>DE_FD_CardAmount</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>18</span>, <span>2</span>) <span>NULL</span><span>, </span><span>28</span> <span>[</span><span>DE_FD_VolumeTotals</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>18</span>, <span>2</span>) <span>NULL</span><span>, </span><span>29</span> <span>[</span><span>DE_FD_AmountTotals</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>18</span>, <span>2</span>) <span>NULL</span><span>, </span><span>30</span> <span>[</span><span>I_FD_ISSend</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NULL</span><span>, </span><span>31</span> <span>[</span><span>VC_FD_CardMoneyauthFile</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NULL</span><span>, </span><span>32</span> <span>[</span><span>D_Month</span><span>]</span> <span>[</span><span>datetime</span><span>]</span> <span>NULL</span><span>, </span><span>33</span> <span>CONSTRAINT</span> <span>[</span><span>PK_CT_FuelingData_1</span><span>]</span> <span>PRIMARY</span> <span>KEY</span> <span>CLUSTERED</span> <span>34</span> <span>( </span><span>35</span> <span>[</span><span>VC_FD_No</span><span>]</span> <span>ASC</span> <span>36</span> )<span>WITH</span> (PAD_INDEX <span>=</span> <span>OFF</span>, STATISTICS_NORECOMPUTE <span>=</span> <span>OFF</span>, IGNORE_DUP_KEY <span>=</span> <span>OFF</span>, ALLOW_ROW_LOCKS <span>=</span> <span>ON</span>, ALLOW_PAGE_LOCKS <span>=</span> <span>ON</span>) <span>ON</span> <span>[</span><span>PRIMARY</span><span>]</span> <span>37</span> ) <span>ON</span> <span>[</span><span>PRIMARY</span><span>]</span> <span>38</span> <span>39</span> <span>GO</span> <span>40</span> <span>SET</span> ANSI_PADDING <span>OFF</span>
CT_InhouseCard表
<span> 1</span> <span>USE</span> <span>[</span><span>GPOSDB</span><span>]</span> <span> 2</span> <span>GO</span> <span> 3</span> <span>/*</span><span>***** 对象: Table [dbo].[CT_InhouseCard] 脚本日期: 08/24/2013 10:59:58 *****</span><span>*/</span> <span> 4</span> <span>SET</span> ANSI_NULLS <span>ON</span> <span> 5</span> <span>GO</span> <span> 6</span> <span>SET</span> QUOTED_IDENTIFIER <span>ON</span> <span> 7</span> <span>GO</span> <span> 8</span> <span>SET</span> ANSI_PADDING <span>ON</span> <span> 9</span> <span>GO</span> <span>10</span> <span>CREATE</span> <span>TABLE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>CT_InhouseCard</span><span>]</span><span>( </span><span>11</span> <span>[</span><span>RecordNO</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>NOT</span> <span>NULL</span><span>, </span><span>12</span> <span>[</span><span>VC_IC_CardNO</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NOT</span> <span>NULL</span><span>, </span><span>13</span> <span>[</span><span>VC_IC_PhysicalNO</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NULL</span><span>, </span><span>14</span> <span>[</span><span>I_IC_CardType</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NULL</span><span>, </span><span>15</span> <span>[</span><span>VC_IC_UserName</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NULL</span><span>, </span><span>16</span> <span>[</span><span>VC_IC_JobNO</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NULL</span><span>, </span><span>17</span> <span>[</span><span>VC_IC_UserID</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NULL</span><span>, </span><span>18</span> <span>[</span><span>VC_IC_Password</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NULL</span><span>, </span><span>19</span> <span>[</span><span>DE_IC_CardAmount</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>18</span>, <span>2</span>) <span>NULL</span><span>, </span><span>20</span> <span>[</span><span>DE_IC_AppendAmount</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>18</span>, <span>2</span>) <span>NULL</span><span>, </span><span>21</span> <span>[</span><span>DE_IC_ConsumerAmount</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>18</span>, <span>2</span>) <span>NULL</span><span>, </span><span>22</span> <span>[</span><span>I_IC_ISLost</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NULL</span><span>, </span><span>23</span> <span>[</span><span>D_IC_UsedDateTime</span><span>]</span> <span>[</span><span>datetime</span><span>]</span> <span>NULL</span><span>, </span><span>24</span> <span>[</span><span>D_IC_UselifeDateTime</span><span>]</span> <span>[</span><span>datetime</span><span>]</span> <span>NULL</span><span>, </span><span>25</span> <span>[</span><span>I_IC_IssueStationNO</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NULL</span><span>, </span><span>26</span> <span>[</span><span>VC_IC_IssuerNO</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NULL</span><span>, </span><span>27</span> <span>[</span><span>D_IC_IssueDateTime</span><span>]</span> <span>[</span><span>datetime</span><span>]</span> <span>NULL</span><span>, </span><span>28</span> <span>[</span><span>D_IC_LastUpdateDateTime</span><span>]</span> <span>[</span><span>datetime</span><span>]</span> <span>NULL</span><span>, </span><span>29</span> <span>[</span><span>I_IC_CardStatus</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NULL</span><span>, </span><span>30</span> <span>[</span><span>VC_IC_Remark</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>256</span>) <span>NULL</span><span>, </span><span>31</span> <span>CONSTRAINT</span> <span>[</span><span>PK_CT_InhouseCard</span><span>]</span> <span>PRIMARY</span> <span>KEY</span> <span>CLUSTERED</span> <span>32</span> <span>( </span><span>33</span> <span>[</span><span>VC_IC_CardNO</span><span>]</span> <span>ASC</span> <span>34</span> )<span>WITH</span> (PAD_INDEX <span>=</span> <span>OFF</span>, STATISTICS_NORECOMPUTE <span>=</span> <span>OFF</span>, IGNORE_DUP_KEY <span>=</span> <span>OFF</span>, ALLOW_ROW_LOCKS <span>=</span> <span>ON</span>, ALLOW_PAGE_LOCKS <span>=</span> <span>ON</span>) <span>ON</span> <span>[</span><span>PRIMARY</span><span>]</span> <span>35</span> ) <span>ON</span> <span>[</span><span>PRIMARY</span><span>]</span> <span>36</span> <span>37</span> <span>GO</span> <span>38</span> <span>SET</span> ANSI_PADDING <span>OFF</span>
测试脚本
因为这个是客户的数据库,本来里面已经有数据了,所以在测试之前先更新两个表的统计信息,以做到公正
<span>1</span> <span>USE</span> <span>[</span><span>GPOSDB</span><span>]</span> <span>2</span> <span>GO</span> <span>3</span> <span>UPDATE</span> <span>STATISTICS</span><span> CT_FuelingData </span><span>4</span> <span>UPDATE</span> <span>STATISTICS</span><span> CT_InhouseCard </span><span>5</span> <span>GO</span>
IN语句
<span> 1</span> <span>USE</span> <span>[</span><span>GPOSDB</span><span>]</span> <span> 2</span> <span>GO</span> <span> 3</span> <span>DBCC</span><span> DROPCLEANBUFFERS </span><span> 4</span> <span>GO</span> <span> 5</span> <span>DBCC</span><span> FREEPROCCACHE </span><span> 6</span> <span>GO</span> <span> 7</span> <span>SET</span> <span>STATISTICS</span> IO <span>ON</span> <span> 8</span> <span>GO</span> <span> 9</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span> <span>10</span> <span>GO</span> <span>11</span> <span>SET</span> <span>STATISTICS</span> PROFILE <span>ON</span> <span>12</span> <span>GO</span> <span>13</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>CT_FuelingData</span><span>]</span> <span>WHERE</span> <span>[</span><span>VC_FD_Cardno</span><span>]</span> <span>IN</span> (<span>SELECT</span> <span>[</span><span>VC_IC_CardNO</span><span>]</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>CT_InhouseCard</span><span>]</span>)
EXISTS语句
<span> 1</span> <span>USE</span> <span>[</span><span>GPOSDB</span><span>]</span> <span> 2</span> <span>GO</span> <span> 3</span> <span>DBCC</span><span> DROPCLEANBUFFERS </span><span> 4</span> <span>GO</span> <span> 5</span> <span>DBCC</span><span> FREEPROCCACHE </span><span> 6</span> <span>GO</span> <span> 7</span> <span>SET</span> <span>STATISTICS</span> IO <span>ON</span> <span> 8</span> <span>GO</span> <span> 9</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span> <span>10</span> <span>GO</span> <span>11</span> <span>SET</span> <span>STATISTICS</span> PROFILE <span>ON</span> <span>12</span> <span>GO</span> <span>13</span> <span>SELECT</span> <span>*</span> <span>14</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>CT_FuelingData</span><span>]</span> <span>15</span> <span>WHERE</span> <span>EXISTS</span> ( <span>SELECT</span> <span>[</span><span>VC_IC_CardNO</span><span>]</span> <span>16</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>CT_InhouseCard</span><span>]</span> <span>17</span> <span>WHERE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>CT_FuelingData</span><span>]</span>.<span>[</span><span>VC_FD_Cardno</span><span>]</span> <span>=</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>CT_InhouseCard</span><span>]</span>.<span>[</span><span>VC_IC_CardNO</span><span>]</span> )
测试结果
IN语句
<span> 1</span> <span>SQL Server 执行时间: </span><span> 2</span> CPU 时间 <span>=</span> <span>0</span> 毫秒,占用时间 <span>=</span> <span>2</span><span> 毫秒。 </span><span> 3</span> <span>SQL Server 分析和编译时间: </span><span> 4</span> CPU 时间 <span>=</span> <span>0</span> 毫秒,占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 5</span> <span> 6</span> <span>SQL Server 执行时间: </span><span> 7</span> CPU 时间 <span>=</span> <span>0</span> 毫秒,占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 8</span> <span>SQL Server 分析和编译时间: </span><span> 9</span> CPU 时间 <span>=</span> <span>0</span> 毫秒,占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span>10</span> <span>11</span> <span>SQL Server 执行时间: </span><span>12</span> CPU 时间 <span>=</span> <span>0</span> 毫秒,占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span>13</span> <span>SQL Server 分析和编译时间: </span><span>14</span> CPU 时间 <span>=</span> <span>0</span> 毫秒,占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span>15</span> <span>16</span> <span>SQL Server 执行时间: </span><span>17</span> CPU 时间 <span>=</span> <span>0</span> 毫秒,占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span>18</span> <span>SQL Server 分析和编译时间: </span><span>19</span> CPU 时间 <span>=</span> <span>31</span> 毫秒,占用时间 <span>=</span> <span>67</span><span> 毫秒。 </span><span>20</span> <span>21</span> (<span>167</span><span> 行受影响) </span><span>22</span> 表 <span>'</span><span>Worktable</span><span>'</span>。扫描计数 <span>0</span>,逻辑读取 <span>0</span> 次,物理读取 <span>0</span> 次,预读 <span>0</span> 次,lob 逻辑读取 <span>0</span> 次,lob 物理读取 <span>0</span> 次,lob 预读 <span>0</span><span> 次。 </span><span>23</span> 表 <span>'</span><span>CT_FuelingData</span><span>'</span>。扫描计数 <span>1</span>,逻辑读取 <span>31</span> 次,物理读取 <span>1</span> 次,预读 <span>64</span> 次,lob 逻辑读取 <span>0</span> 次,lob 物理读取 <span>0</span> 次,lob 预读 <span>0</span><span> 次。 </span><span>24</span> 表 <span>'</span><span>CT_InhouseCard</span><span>'</span>。扫描计数 <span>1</span>,逻辑读取 <span>2</span> 次,物理读取 <span>1</span> 次,预读 <span>0</span> 次,lob 逻辑读取 <span>0</span> 次,lob 物理读取 <span>0</span> 次,lob 预读 <span>0</span><span> 次。 </span><span>25</span> <span>26</span> (<span>4</span><span> 行受影响) </span><span>27</span> <span>28</span> <span>SQL Server 执行时间: </span><span>29</span> CPU 时间 <span>=</span> <span>16</span> 毫秒,占用时间 <span>=</span> <span>192</span> 毫秒。
EXISTS语句
<span> 1</span> <span>SQL Server 分析和编译时间: </span><span> 2</span> CPU 时间 <span>=</span> <span>0</span> 毫秒,占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 3</span> <span> 4</span> <span>SQL Server 执行时间: </span><span> 5</span> CPU 时间 <span>=</span> <span>0</span> 毫秒,占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 6</span> <span>SQL Server 分析和编译时间: </span><span> 7</span> CPU 时间 <span>=</span> <span>0</span> 毫秒,占用时间 <span>=</span> <span>34</span><span> 毫秒。 </span><span> 8</span> <span> 9</span> (<span>167</span><span> 行受影响) </span><span>10</span> 表 <span>'</span><span>Worktable</span><span>'</span>。扫描计数 <span>0</span>,逻辑读取 <span>0</span> 次,物理读取 <span>0</span> 次,预读 <span>0</span> 次,lob 逻辑读取 <span>0</span> 次,lob 物理读取 <span>0</span> 次,lob 预读 <span>0</span><span> 次。 </span><span>11</span> 表 <span>'</span><span>CT_FuelingData</span><span>'</span>。扫描计数 <span>1</span>,逻辑读取 <span>31</span> 次,物理读取 <span>1</span> 次,预读 <span>64</span> 次,lob 逻辑读取 <span>0</span> 次,lob 物理读取 <span>0</span> 次,lob 预读 <span>0</span><span> 次。 </span><span>12</span> 表 <span>'</span><span>CT_InhouseCard</span><span>'</span>。扫描计数 <span>1</span>,逻辑读取 <span>2</span> 次,物理读取 <span>1</span> 次,预读 <span>0</span> 次,lob 逻辑读取 <span>0</span> 次,lob 物理读取 <span>0</span> 次,lob 预读 <span>0</span><span> 次。 </span><span>13</span> <span>14</span> (<span>4</span><span> 行受影响) </span><span>15</span> <span>16</span> <span>SQL Server 执行时间: </span><span>17</span> CPU 时间 <span>=</span> <span>0</span> 毫秒,占用时间 <span>=</span> <span>163</span> 毫秒。
大家可以看到除了执行时间有一点差别,IO是一样的
因为数据量比较大,所以两个查询都用到了Worktable(中间表)来存储中间结果
IN语句的执行计划
EXISTS语句的执行计划
从执行计划可以看到两个SQL语句的开销都是一样的,而且大家都使用了右半连接(Right Semi Join)
至于什么是半连接(Semi-join)大家可以看一下这篇文章:SQL Join的一些总结
总结
从上面实际的执行来比较,,IN语句和EXISTS语句基本上都是一样的效率
如有不对的地方,欢迎大家来拍砖o(∩_∩)o

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

For objects with the same name that already exist in the SQL Server database, the following steps need to be taken: Confirm the object type (table, view, stored procedure). IF NOT EXISTS can be used to skip creation if the object is empty. If the object has data, use a different name or modify the structure. Use DROP to delete existing objects (use caution, backup recommended). Check for schema changes to make sure there are no references to deleted or renamed objects.

The import steps are as follows: Copy the MDF file to SQL Server's data directory (usually C:\Program Files\Microsoft SQL Server\MSSQL\DATA). In SQL Server Management Studio (SSMS), open the database and select Attach. Click the Add button and select the MDF file. Confirm the database name and click the OK button.

When the SQL Server service fails to start, here are some steps to resolve: Check the error log to determine the root cause. Make sure the service account has permission to start the service. Check whether dependency services are running. Disable antivirus software. Repair SQL Server installation. If the repair does not work, reinstall SQL Server.

To view the SQL Server port number: Open SSMS and connect to the server. Find the server name in Object Explorer, right-click it and select Properties. In the Connection tab, view the TCP Port field.

If you accidentally delete a SQL Server database, you can take the following steps to recover: stop database activity; back up log files; check database logs; recovery options: restore from backup; restore from transaction log; use DBCC CHECKDB; use third-party tools. Please back up your database regularly and enable transaction logging to prevent data loss.

SQL Server database files are usually stored in the following default location: Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data The database file location can be customized by modifying the database file path setting.

If the SQL Server installation fails, you can clean it up by following these steps: Uninstall SQL Server Delete registry keys Delete files and folders Restart the computer

SQL Server English installation can be changed to Chinese by following the following steps: download the corresponding language pack; stop the SQL Server service; install the language pack; change the instance language; change the user interface language; restart the application.
