Inhaltsverzeichnis
SQLSERVER语句 in和exists哪个效率高本人测试证明
exists
sqlserver
Welche
语句
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 )
Nach dem Login kopieren
上面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 )
Nach dem Login kopieren
上面的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>
Nach dem Login kopieren
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>
Nach dem Login kopieren
测试脚本
因为这个是客户的数据库，本来里面已经有数据了，所以在测试之前先更新两个表的统计信息，以做到公正
<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>
Nach dem Login kopieren
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>)
Nach dem Login kopieren
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> )
Nach dem Login kopieren
测试结果
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> 毫秒。
Nach dem Login kopieren
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> 毫秒。
Nach dem Login kopieren
大家可以看到除了执行时间有一点差别，IO是一样的
因为数据量比较大，所以两个查询都用到了Worktable（中间表）来存储中间结果
IN语句的执行计划
EXISTS语句的执行计划
从执行计划可以看到两个SQL语句的开销都是一样的，而且大家都使用了右半连接（Right Semi Join）
至于什么是半连接（Semi-join）大家可以看一下这篇文章：SQL Join的一些总结
总结
从上面实际的执行来比较,，IN语句和EXISTS语句基本上都是一样的效率
如有不对的地方，欢迎大家来拍砖o(∩_∩)o
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Heiße KI -Werkzeuge
Undresser.AI Undress
KI-gestützte App zum Erstellen realistischer Aktfotos
AI Clothes Remover
Online-KI-Tool zum Entfernen von Kleidung aus Fotos.
Undress AI Tool
Ausziehbilder kostenlos
Clothoff.io
KI-Kleiderentferner
AI Hentai Generator
Erstellen Sie kostenlos Ai Hentai.
Heißer Artikel
R.E.P.O. Energiekristalle erklärten und was sie tun (gelber Kristall)
3 Wochen vor
By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Beste grafische Einstellungen
3 Wochen vor
By 尊渡假赌尊渡假赌尊渡假赌
Assassin's Creed Shadows: Seashell Riddle -Lösung
2 Wochen vor
By DDD
R.E.P.O. So reparieren Sie Audio, wenn Sie niemanden hören können
3 Wochen vor
By 尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: Wie man alles in Myrise freischaltet
4 Wochen vor
By 尊渡假赌尊渡假赌尊渡假赌
Heiße Werkzeuge
Notepad++7.3.1
Einfach zu bedienender und kostenloser Code-Editor
SublimeText3 chinesische Version
Chinesische Version, sehr einfach zu bedienen
Senden Sie Studio 13.0.1
Leistungsstarke integrierte PHP-Entwicklungsumgebung
Dreamweaver CS6
Visuelle Webentwicklungstools
SublimeText3 Mac-Version
Codebearbeitungssoftware auf Gottesniveau (SublimeText3)
Heiße Themen
CakePHP-Tutorial
1377
52
1377
52
So lösen Sie das Problem, dass das genannte Objekt bereits in der SQLServer-Datenbank vorhanden ist
Apr 05, 2024 pm 09:42 PM
Für Objekte mit demselben Namen, die bereits in der SQL Server-Datenbank vorhanden sind, müssen folgende Schritte ausgeführt werden: Bestätigen Sie den Objekttyp (Tabelle, Ansicht, gespeicherte Prozedur). Mit IF NOT EXISTS kann die Erstellung übersprungen werden, wenn das Objekt leer ist. Wenn das Objekt Daten enthält, verwenden Sie einen anderen Namen oder ändern Sie die Struktur. Verwenden Sie DROP, um vorhandene Objekte zu löschen (Vorsicht, Sicherung empfohlen). Suchen Sie nach Schemaänderungen, um sicherzustellen, dass keine Verweise auf gelöschte oder umbenannte Objekte vorhanden sind.
So importieren Sie eine MDF-Datei in SQL Server
Apr 08, 2024 am 11:41 AM
Die Importschritte sind wie folgt: Kopieren Sie die MDF-Datei in das Datenverzeichnis von SQL Server (normalerweise C:\Programme\Microsoft SQL Server\MSSQL\DATA). Öffnen Sie in SQL Server Management Studio (SSMS) die Datenbank und wählen Sie Anhängen aus. Klicken Sie auf die Schaltfläche „Hinzufügen“ und wählen Sie die MDF-Datei aus. Bestätigen Sie den Datenbanknamen und klicken Sie auf die Schaltfläche OK.
Was tun, wenn der SQLServer-Dienst nicht gestartet werden kann?
Apr 05, 2024 pm 10:00 PM
Wenn der SQL Server-Dienst nicht gestartet werden kann, können Sie die folgenden Schritte beheben: Überprüfen Sie das Fehlerprotokoll, um die Grundursache zu ermitteln. Stellen Sie sicher, dass das Dienstkonto über die Berechtigung zum Starten des Dienstes verfügt. Überprüfen Sie, ob Abhängigkeitsdienste ausgeführt werden. Deaktivieren Sie die Antivirensoftware. Reparieren Sie die SQL Server-Installation. Wenn die Reparatur nicht funktioniert, installieren Sie SQL Server neu.
So überprüfen Sie die SQLServer-Portnummer
Apr 05, 2024 pm 09:57 PM
So zeigen Sie die SQL Server-Portnummer an: Öffnen Sie SSMS und stellen Sie eine Verbindung zum Server her. Suchen Sie den Servernamen im Objekt-Explorer, klicken Sie mit der rechten Maustaste darauf und wählen Sie Eigenschaften. Sehen Sie sich auf der Registerkarte „Verbindung“ das Feld „TCP-Port“ an.
Wo ist die SQLServer-Datenbank?
Apr 05, 2024 pm 08:21 PM
SQL Server-Datenbankdateien werden normalerweise am folgenden Standardspeicherort gespeichert: Windows: C:\Programme\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data Der Speicherort der Datenbankdatei kann durch Ändern des Datenbankdateipfads angepasst werden Einstellung.
So stellen Sie eine versehentlich gelöschte Datenbank in SQL Server wieder her
Apr 05, 2024 pm 10:39 PM
Wenn Sie eine SQL Server-Datenbank versehentlich löschen, können Sie die Datenbankaktivität stoppen; Party-Tools. Bitte sichern Sie Ihre Datenbank regelmäßig und aktivieren Sie die Transaktionsprotokollierung, um Datenverlust zu verhindern.
Wie lösche ich SQL Server, wenn die Installation fehlschlägt?
Apr 05, 2024 pm 11:27 PM
Wenn die SQL Server-Installation fehlschlägt, können Sie sie mit den folgenden Schritten bereinigen: Deinstallieren Sie SQL Server. Löschen Sie Registrierungsschlüssel. Löschen Sie Dateien und Ordner. Starten Sie den Computer neu
So ändern Sie die englische SQLServer-Installation in Chinesisch
Apr 05, 2024 pm 10:21 PM
Die englische Installation von SQL Server kann wie folgt geändert werden: Laden Sie den SQL Server-Dienst herunter. Ändern Sie die Sprache der Benutzeroberfläche.
