Table des matières
SQLSERVER语句 in和exists哪个效率高本人测试证明
exists
sqlserver
Lequel
语句
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 )
Copier après la connexion
上面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 )
Copier après la connexion
上面的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>
Copier après la connexion
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>
Copier après la connexion
测试脚本
因为这个是客户的数据库，本来里面已经有数据了，所以在测试之前先更新两个表的统计信息，以做到公正
<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>
Copier après la connexion
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>)
Copier après la connexion
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> )
Copier après la connexion
测试结果
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> 毫秒。
Copier après la connexion
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> 毫秒。
Copier après la connexion
大家可以看到除了执行时间有一点差别，IO是一样的
因为数据量比较大，所以两个查询都用到了Worktable（中间表）来存储中间结果
IN语句的执行计划
EXISTS语句的执行计划
从执行计划可以看到两个SQL语句的开销都是一样的，而且大家都使用了右半连接（Right Semi Join）
至于什么是半连接（Semi-join）大家可以看一下这篇文章：SQL Join的一些总结
总结
从上面实际的执行来比较,，IN语句和EXISTS语句基本上都是一样的效率
如有不对的地方，欢迎大家来拍砖o(∩_∩)o
Déclaration de ce site Web
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Outils d'IA chauds
Undresser.AI Undress
Application basée sur l'IA pour créer des photos de nu réalistes
AI Clothes Remover
Outil d'IA en ligne pour supprimer les vêtements des photos.
Undress AI Tool
Images de déshabillage gratuites
Clothoff.io
Dissolvant de vêtements AI
AI Hentai Generator
Générez AI Hentai gratuitement.
Article chaud
R.E.P.O. Crystals d'énergie expliqués et ce qu'ils font (cristal jaune)
3 Il y a quelques semaines
By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Meilleurs paramètres graphiques
3 Il y a quelques semaines
By 尊渡假赌尊渡假赌尊渡假赌
Assassin's Creed Shadows: Solution d'énigmes de coquille
1 Il y a quelques semaines
By DDD
R.E.P.O. Comment réparer l'audio si vous n'entendez personne
3 Il y a quelques semaines
By 尊渡假赌尊渡假赌尊渡假赌
Où trouver la courte de la grue à atomide atomique
1 Il y a quelques semaines
By DDD
Outils chauds
Bloc-notes++7.3.1
Éditeur de code facile à utiliser et gratuit
SublimeText3 version chinoise
Version chinoise, très simple à utiliser
Envoyer Studio 13.0.1
Puissant environnement de développement intégré PHP
Dreamweaver CS6
Outils de développement Web visuel
SublimeText3 version Mac
Logiciel d'édition de code au niveau de Dieu (SublimeText3)
Sujets chauds
Tutoriel CakePHP
1359
52
1359
52
Comment résoudre le problème selon lequel l'objet nommé existe déjà dans la base de données sqlserver
Apr 05, 2024 pm 09:42 PM
Pour les objets du même nom qui existent déjà dans la base de données SQL Server, les étapes suivantes doivent être suivies : Confirmez le type d'objet (table, vue, procédure stockée). IF NOT EXISTS peut être utilisé pour ignorer la création si l'objet est vide. Si l'objet contient des données, utilisez un nom différent ou modifiez la structure. Utilisez DROP pour supprimer des objets existants (soyez prudent, sauvegarde recommandée). Vérifiez les modifications de schéma pour vous assurer qu'il n'y a aucune référence à des objets supprimés ou renommés.
Comment importer un fichier mdf dans sqlserver
Apr 08, 2024 am 11:41 AM
Les étapes d'importation sont les suivantes : Copiez le fichier MDF dans le répertoire de données de SQL Server (généralement C:\Program Files\Microsoft SQL Server\MSSQL\DATA). Dans SQL Server Management Studio (SSMS), ouvrez la base de données et sélectionnez Attacher. Cliquez sur le bouton Ajouter et sélectionnez le fichier MDF. Confirmez le nom de la base de données et cliquez sur le bouton OK.
Que faire si le service sqlserver ne peut pas être démarré
Apr 05, 2024 pm 10:00 PM
Lorsque le service SQL Server ne parvient pas à démarrer, voici quelques étapes à résoudre : Consultez le journal des erreurs pour déterminer la cause première. Assurez-vous que le compte de service est autorisé à démarrer le service. Vérifiez si les services de dépendance sont en cours d'exécution. Désactivez le logiciel antivirus. Réparez l'installation de SQL Server. Si la réparation ne fonctionne pas, réinstallez SQL Server.
Comment vérifier le numéro de port sqlserver
Apr 05, 2024 pm 09:57 PM
Pour afficher le numéro de port SQL Server : Ouvrez SSMS et connectez-vous au serveur. Recherchez le nom du serveur dans l'Explorateur d'objets, cliquez dessus avec le bouton droit et sélectionnez Propriétés. Dans l'onglet Connexion, affichez le champ Port TCP.
Où est la base de données sqlserver ?
Apr 05, 2024 pm 08:21 PM
Les fichiers de base de données SQL Server sont généralement stockés à l'emplacement par défaut suivant : Windows : C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux : /var/opt/mssql/data L'emplacement du fichier de base de données peut être personnalisé en modifiant le chemin du fichier de base de données. paramètre.
Comment récupérer une base de données supprimée accidentellement dans SQLserver
Apr 05, 2024 pm 10:39 PM
Si vous supprimez accidentellement une base de données SQL Server, vous pouvez suivre les étapes suivantes pour la récupérer : arrêter l'activité de la base de données ; sauvegarder les fichiers journaux ; vérifier les options de récupération : restaurer à partir d'une sauvegarde ; utiliser DBCC CHECKDB ; outils de fête. Veuillez sauvegarder régulièrement votre base de données et activer la journalisation des transactions pour éviter toute perte de données.
Comment supprimer sqlserver si l'installation échoue ?
Apr 05, 2024 pm 11:27 PM
Si l'installation de SQL Server échoue, vous pouvez la nettoyer en suivant ces étapes : Désinstaller SQL Server Supprimer les clés de registre Supprimer les fichiers et dossiers Redémarrer l'ordinateur
Comment changer l'installation anglaise de sqlserver en chinois
Apr 05, 2024 pm 10:21 PM
L'installation de SQL Server en anglais peut être modifiée en chinois en suivant les étapes suivantes : téléchargez le module linguistique correspondant ; arrêtez le service SQL Server ; modifiez la langue de l'instance ; modifiez la langue de l'interface utilisateur ;
