Jadual Kandungan

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

Rumah

pangkalan data

tutorial mysql

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:19 PM

exists sqlserver yang mana kenyataan

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 )

Salin selepas log masuk

上面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 )

Salin selepas log masuk

上面的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>

Salin selepas log masuk

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>

Salin selepas log masuk

测试脚本

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

<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>

Salin selepas log masuk

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>)

Salin selepas log masuk

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> )

Salin selepas log masuk

测试结果

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> 毫秒。

Salin selepas log masuk

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> 毫秒。

Salin selepas log masuk

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

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

IN语句的执行计划

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

EXISTS语句的执行计划

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

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

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

总结

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

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

Kenyataan Laman Web ini

Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn

Alat AI Hot

Undresser.AI Undress

Apl berkuasa AI untuk mencipta foto bogel yang realistik

AI Clothes Remover

Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.

Undress AI Tool

Gambar buka pakaian secara percuma

Clothoff.io

Penyingkiran pakaian AI

AI Hentai Generator

Menjana ai hentai secara percuma.

Tunjukkan Lagi

Artikel Panas

R.E.P.O. Kristal tenaga dijelaskan dan apa yang mereka lakukan (kristal kuning)

3 minggu yang lalu By 尊渡假赌尊渡假赌尊渡假赌

R.E.P.O. Tetapan grafik terbaik

3 minggu yang lalu By 尊渡假赌尊渡假赌尊渡假赌

Assassin's Creed Shadows: Penyelesaian Riddle Seashell

2 minggu yang lalu By DDD

R.E.P.O. Cara Memperbaiki Audio Jika anda tidak dapat mendengar sesiapa

3 minggu yang lalu By 尊渡假赌尊渡假赌尊渡假赌

WWE 2K25: Cara Membuka Segala -galanya Di Myrise

4 minggu yang lalu By 尊渡假赌尊渡假赌尊渡假赌

Tunjukkan Lagi

Alat panas

Notepad++7.3.1

Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina

Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1

Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6

Alat pembangunan web visual

SublimeText3 versi Mac

Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

Tunjukkan Lagi

Topik panas

Di manakah pintu masuk log masuk untuk e-mel gmail?

7470

Tutorial CakePHP

1377

Apakah format nama akaun stim

kunci pengaktifan win11 kekal

Sambungan NYT menunjukkan dan jawapan

Tunjukkan Lagi

Related knowledge

Bagaimana untuk menyelesaikan masalah yang objek bernama sudah wujud dalam pangkalan data sqlserver Apr 05, 2024 pm 09:42 PM

Untuk objek dengan nama yang sama yang telah wujud dalam pangkalan data SQL Server, langkah berikut perlu diambil: Sahkan jenis objek (jadual, paparan, prosedur tersimpan). IF NOT EXISTS boleh digunakan untuk melangkau penciptaan jika objek kosong. Jika objek mempunyai data, gunakan nama lain atau ubah suai struktur. Gunakan DROP untuk memadam objek sedia ada (berhati-hati, sandaran disyorkan). Semak perubahan skema untuk memastikan tiada rujukan kepada objek yang dipadam atau dinamakan semula.

Bagaimana untuk mengimport fail mdf ke dalam sqlserver Apr 08, 2024 am 11:41 AM

Langkah import adalah seperti berikut: Salin fail MDF ke direktori data SQL Server (biasanya C:\Program Files\Microsoft SQL Server\MSSQL\DATA). Dalam SQL Server Management Studio (SSMS), buka pangkalan data dan pilih Lampirkan. Klik butang Tambah dan pilih fail MDF. Sahkan nama pangkalan data dan klik butang OK.

Apa yang perlu dilakukan jika perkhidmatan sqlserver tidak dapat dimulakan Apr 05, 2024 pm 10:00 PM

Apabila perkhidmatan SQL Server gagal dimulakan, berikut ialah beberapa langkah untuk diselesaikan: Semak log ralat untuk menentukan punca. Pastikan akaun perkhidmatan mempunyai kebenaran untuk memulakan perkhidmatan. Semak sama ada perkhidmatan pergantungan sedang berjalan. Lumpuhkan perisian antivirus. Membaiki pemasangan SQL Server. Jika pembaikan tidak berjaya, pasang semula SQL Server.

Bagaimana untuk menyemak nombor port sqlserver Apr 05, 2024 pm 09:57 PM

Untuk melihat nombor port SQL Server: Buka SSMS dan sambung ke pelayan. Cari nama pelayan dalam Object Explorer, klik kanan padanya dan pilih Properties. Dalam tab Sambungan, lihat medan Port TCP.

Di manakah pangkalan data sqlserver? Apr 05, 2024 pm 08:21 PM

Fail pangkalan data SQL Server biasanya disimpan di lokasi lalai berikut: Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data Lokasi fail pangkalan data boleh disesuaikan dengan mengubah suai laluan fail pangkalan data tetapan.

Bagaimana untuk memulihkan pangkalan data yang dipadam secara tidak sengaja dalam sqlserver Apr 05, 2024 pm 10:39 PM

Jika anda secara tidak sengaja memadamkan pangkalan data SQL Server, anda boleh mengambil langkah-langkah berikut untuk memulihkan: hentikan aktiviti pangkalan data periksa log pangkalan data: pulihkan daripada sandaran menggunakan DBCC; alatan parti. Sila sandarkan pangkalan data anda dengan kerap dan dayakan pengelogan transaksi untuk mengelakkan kehilangan data.

Bagaimana untuk memadam sqlserver jika pemasangan gagal? Apr 05, 2024 pm 11:27 PM

Jika pemasangan SQL Server gagal, anda boleh membersihkannya dengan mengikuti langkah berikut: Nyahpasang SQL Server Padam kekunci pendaftaran Padam fail dan folder Mulakan semula komputer

Bagaimana untuk menukar pemasangan bahasa Inggeris sqlserver kepada bahasa Cina Apr 05, 2024 pm 10:21 PM

Pemasangan Bahasa Inggeris Pelayan SQL boleh ditukar kepada bahasa Cina dengan mengikuti langkah berikut: muat turun pek bahasa yang sepadan dengan memasang pek bahasa;

See all articles