同一段SQL,where后字段赋值不一样，查询耗时有很大差别，为什么？

Question

具体耗时多少没有测，MSSQL2000,SQL如下：select ord_id,ord_no,qty
from s_order_m
where ord_id='A'
  and mat_no in(select mat_no from mat where type_id='01' and type_rem='AAA')返回行400条，很快。查询分析器 右下角显示耗时：0:00:00
如果改成(ord_id='A'--->ord_id='B')：
select ord_id,ord_no,qty
from s_order_m
where ord_id='B'
  and mat_no in(select mat_no from mat where type_id='01' and type_rem='AAA')返回行1条，稍慢。查询分析器 右下角显示耗时：0:00:02说明：A代表正常销货，B是退货。主键：ord_id + ord_no若是改成如下，即将具体字段换成：‘*’，查询耗时也是 0:00:00.
select * from s_order_m
where ord_id='B'
  and mat_no in(select mat_no from mat where type_id='01' and type_rem='AAA')为什么会出现以上这两种情况：
同样的SQL，赋给字段的值不一样，查询效率不一样（在查询分析器看执行计划是不一样的，但看不太懂），返回行少的反而慢？
把字段1、字段2...换成 * 后，反而会快，select * 不是效率会低吗？多谢！！！

Answer 1

有执行计划看看吗？如果不列中A的值很少，那么你where ord_id='A'的时候就会筛选掉大量数据，剩下少量数据给 mat_no in这句来处理。而B值如果数据量大，那么where ord_id='B'的时候可能就剩下还是很多的数据。另外，你的查询最少要执行2~3次，拿到的时间才比较有价值，因为第一次在做编译和优化。

Answer 2

改成(ord_id='A'--->ord_id='B')：这个更改查询速度比更改之前慢，可能是符合ord_id='B'的数据量比较大。这个是可能的情况把字段1、字段2...换成 * 后，反而会快？ 这个一次的执行计划时间不准确吧，应该不会出现这种情况，你多执行几次看看。

Answer 3

回复1楼，2楼：我的疑惑也跟你们一样，以前从未遇到过。
补充说明：
1. 查询执行了不止2~~3次，不同的时间段测试了N次了。
2. ord_id='B'的数据量很少，绝大部分是的ord_id='A'的行。
3. mat in () 子查询返回行不太多，50行左右。

Answer 4

dbcc dropcleanbuffers   
/*从缓冲池中删除所有清除缓冲区。*/dbcc freeproccache   
/*从过程缓存中删除所有元素。*/set statistics io on 
set statistics time on 
go/*--你的sql脚本开始*/然后把你io 信息发上来看看或者自己对比一下

Answer 5

这是面试的时候常问的一道题, 一张表里的id 列有 1 个 1 , 9999 2where id=1 --走索引
where id=2 --全表扫描你可以这样告诉你的面试者说, 这张表走index_seek 还是 scan  在于需要查找的数据占整张表的比例的大小

Answer 6

这是面试时普遍的一道题,
一张表走索引还是走全表扫描在于 取出的数据占整张表的百分比一张表10000 条   id 为1 的一条   id为2 的9999where id=1 --index seek
where id=2 --scan

Answer 7

select a.ord_id,a.ord_no,qty
from s_order_m as a
inner join mat as b on a.mat_no=b.mat_no
where a.ord_id='A' and b.type_id='01' and b.type_rem='AAA'select a.*
from s_order_m as a
inner join mat as b on a.mat_no=b.mat_no
where a.ord_id='A' and b.type_id='01' and b.type_rem='AAA'
这两个执行起来有区别吗？

Answer 8

有的，我也测了，还用exists 测了，都一样。

Answer 9

按道理ord_id='B'会更快啊。ord_id='B'的资料行少

Answer 10

这是你ord_id列有索引,mat_no列缺少索引,但是A的数量较少,B的数量较多,查询A的时候利用了索引,查询B的时候使用了扫描(而且是表扫描或者聚集索引扫描,由第三个查询的速度快推出的)的缘故,第三次查询*速度很快,是因为第二个查询已经执行了表扫描,数据已经缓存在缓冲区,不需要花销IO读取磁盘的缘故
做性能测试需要每次都清空缓存,方法楼上有.有时候要测有缓存时候的性能,就不要记录考虑第一次运行的速度,连续运行,取后面的运行速度

Answer 11

你好，是 ：A的数量较多,B的数量较少，应该是查询B的时候用到了索引吧？？？
不太懂，求解。

Answer 12

把执行计划贴出来
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>

Answer 13

我用的是mssql2000 ，在查询分析器上有你说的三个绿点的图标，应该是‘显示预计的执行计划’。但显示的结果是图形状的，也不能保存！