Table of Contents
SQLSERVR语句 in和exists哪个效率高本人测试证明
 
Home Database Mysql Tutorial SQLSERVER语句 in和exists哪个效率高本人测试证明

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

Jun 07, 2016 pm 03:19 PM
exists sqlserver Which statement

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 )
Copy after login

上面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 )
Copy after login

上面的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>
Copy after login

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>
Copy after login

 


测试脚本

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

<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>
Copy after login

 

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>)
Copy after login

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> )
Copy after login

 


测试结果

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> 毫秒。
Copy after login

 

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> 毫秒。
Copy after login

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

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

IN语句的执行计划

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

EXISTS语句的执行计划

 

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

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

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

 

总结

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

 

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

 

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to solve the problem that the object named already exists in the sqlserver database How to solve the problem that the object named already exists in the sqlserver database Apr 05, 2024 pm 09:42 PM

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.

How to import mdf file into sqlserver How to import mdf file into sqlserver Apr 08, 2024 am 11:41 AM

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.

What to do if the sqlserver service cannot be started What to do if the sqlserver service cannot be started Apr 05, 2024 pm 10:00 PM

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.

How to check sqlserver port number How to check sqlserver port number Apr 05, 2024 pm 09:57 PM

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.

How to recover accidentally deleted database in sqlserver How to recover accidentally deleted database in sqlserver Apr 05, 2024 pm 10:39 PM

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.

Where is the sqlserver database? Where is the sqlserver database? Apr 05, 2024 pm 08:21 PM

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.

How to delete sqlserver if the installation fails? How to delete sqlserver if the installation fails? Apr 05, 2024 pm 11:27 PM

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

How to change sqlserver English installation to Chinese How to change sqlserver English installation to Chinese Apr 05, 2024 pm 10:21 PM

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.

See all articles