SQL 表链接的奇怪问题（子查询速度比整体查询慢！）

Question

代码片段一：select * from 
hjt_edb_wlxqjh_detail
where strWlqdId in (
select strWlqdId
from hjt_edb_wlqd_info
where strGsWlqdId = (
select B.strWlqdId
from hjt_edb_zscjh_info A
inner join hjt_edb_wlqd_info B on A.strWlqdId = B.strWlqdId and B.strDelFlg = dbo.DELETE_FLG_0()
where A.strDelFlg = dbo.DELETE_FLG_0()
and A.strZscJhId = 'strZscJhId_1'
)
)
and strZscJhId = 'strZscJhId_1'
代码片段二：select strWlqdId
from hjt_edb_wlqd_info
where strGsWlqdId = (
select B.strWlqdId
from hjt_edb_zscjh_info A
inner join hjt_edb_wlqd_info B on A.strWlqdId = B.strWlqdId and B.strDelFlg = dbo.DELETE_FLG_0()
where A.strDelFlg = dbo.DELETE_FLG_0()
and A.strZscJhId = 'strZscJhId_1'
)
我的问题是为什么‘代码片段一（1秒内）’比‘代码片段二（18秒）’执行的快呢？
我个人认为‘代码片段二’是‘代码片段一’的子查询，速度应该比‘代码片段一’快，求解？？？
备注：hjt_edb_wlqd_info 100万条数据
      hjt_edb_zscjh_info 10万条数据
      hjt_edb_wlxqjh_detail 10万条数据

Answer 1

select * from 
hjt_edb_wlxqjh_detail
where  strZscJhId = 'strZscJhId_1'多快?结果集多少数据量？

Answer 2

我猜测是因为最外层你还有一个strZscJhId = 'strZscJhId_1'的条件，而在hjt_edb_wlxqjh_detail
中，符合条件的数据行很少。去掉这个条件在执行一下看看结果吧。

Answer 3

如果把‘代码片段一’的“strZscJhId = 'strZscJhId_1'”去掉取得数据就不对了，‘hjt_edb_wlxqjh_detail
中，符合条件的数据行很少’这句话是正确的，但是为什么会出现这种情况呢？子查询不是应该比整体查询快吗？

Answer 4

try:
select strWlqdId
from hjt_edb_wlqd_info
where strGsWlqdId in (
    select B.strWlqdId
    from hjt_edb_zscjh_info A
    inner join hjt_edb_wlqd_info B on A.strWlqdId = B.strWlqdId and A.strDelFlg = B.strDelFlg
    where A.strDelFlg = dbo.DELETE_FLG_0()
    and A.strZscJhId = 'strZscJhId_1'
)

Answer 5

这是个先有蛋还是先有鸡的问题，你的代码片段一，优化器发现and条件后面的等于能筛选出最少的数据，所以他就是用了后面这个等于，对于子查询，优化器在2005之后对in运算符进行了改良，优化器会将in转变为exists，exists具有短路效应，当找到一条记录时exists成立，优化器就认为子查询里面的条件是成立的，不会再去读取更多的数据，所以速度很快

Answer 6

这个这个，你不能理解？
比如说 select * from 
hjt_edb_wlxqjh_detail
where strWlqdId in (  XXX  )
)
and strZscJhId = 'strZscJhId_1'
不要IN条件后 
 select * from 
hjt_edb_wlxqjh_detail
where strZscJhId = 'strZscJhId_1' 只出来一条数据 假设这条数据 strWlqdId = 'A'
然后加上IN条件，发现子查询有有一条结果也等于'A'，那么数据库是不是就可以把这条记录呈现给你了？
就算子查询有100条 都等于'A'，他还是只会返回一条给你，这么做有意义么？事实上，SQL2005之前的数据库会读100条，现在不会

Answer 7

注意，这里是要一个饼，这句话是  select * from 
hjt_edb_wlxqjh_detail
where strWlqdId in (  XXX  )
)
and strZscJhId = 'strZscJhId_1'
中的and 后面的条件告诉我的，如果没有这个条件，就变成了
把能买的都买回来，那就要问所有饼摊啦

Answer 8

不对啊，strZscJhId 不是hjt_edb_wlxqjh_detail主键约束而是一个外键约束，查询出来是几个记录集不是一条的！！

Answer 9

SQL Server 2005 怎么看执行计划呢？望详细谈一下啦。

Answer 10

SQL编辑器工具栏上面,有个三个绿点的图标,点上它运行就可以出执行计划,把它保存后用记事本打开,实际是个XML文件,将XML格式贴出来
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.4035.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="25" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.0033095" StatementText="select * from tl0912&#xD;&#xA;" StatementType="SELECT">
          <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="26" CompileTime="0" CompileCPU="0" CompileMemory="96">
            <RelOp AvgRowSize="332" EstimateCPU="0.000106" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25" LogicalOp="Table Scan" NodeId="0" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0033095">
              <OutputList>
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="single_No" />
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="subcode" />
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="item_num" />
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="Crt_Time" />
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="ID" />
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="ChartID" />
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="WorkNo" />
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="WorkDate" />
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="WorkID" />
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="sect_No2" />
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="MoveMake_parent" />
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="MoveMake_Qty" />
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="TimeCount" />
                <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="IsOut" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="25" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="single_No" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="subcode" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="item_num" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="Crt_Time" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="ID" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="ChartID" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="WorkNo" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="WorkDate" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="WorkID" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="sect_No2" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="MoveMake_parent" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="MoveMake_Qty" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="TimeCount" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" Column="IsOut" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[Jsmis]" Schema="[dbo]" Table="[tl0912]" />
              </TableScan>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>