同样一个查询2008比2000慢3倍以上啊???

这个 SQL 有毛病，TOP 1 没有 ORDER BY，不如用 max 试试

更新统计信息,重建索引,清空执行计划后再试试.-- 更新统计信息
update statistics ERP_Order with fullscan
update statistics ERP_OrderList with fullscan-- 重建索引
dbcc dbreindex('[数据库名].dbo.ERP_Order','',90)
dbcc dbreindex('[数据库名].dbo.ERP_OrderList','',90)-- 清空执行计划
DBCC FREEPROCCACHE

神奇啊,改用max()就正常了
使用top 1 查了一下执行计划,是由于查询计划使用了一个索引假脱机和排序造成花费了50%以上的时间
实际是花费了90%以上的时间在索引假脱机上,百度了一下这个索引假脱机,很多人都遇到过
有什么办法可以避免这个发生啊在2000使用top 1 和 max()是没区别的,一样快,为什么到2008就出现索引假脱机这个怪东西啊
我的语句根本没有排序,为什么执行计划会对ERP_Order.OrderID进行排序呢

这些都试过了,没用.找到原因了,原因就是在top 1 上,改用max()就行了,虽然这个查询使用top 1 和max()对我来说没区别
但以前写的很多语句都习惯使用top 1 的,在2000一直没问题,为什么到2008就有这么大的区别啊

我还是不明白的是,为什么使用top 1 会出现索引假脱机呢而使用max()不会呢
有哪个大牛解析一下使用top 1 产生索引假脱机的原因,好以后避免出现这个写法

神奇啊,改用max()就正常了
使用top 1 查了一下执行计划,是由于查询计划使用了一个索引假脱机和排序造成花费了50%以上的时间
实际是花费了90%以上的时间在索引假脱机上,百度了一下这个索引假脱机,很多人都遇到过
有什么办法可以避免这个发生啊在2000使用top 1 和 max()是没区别的,一样快,为什么到2008就出现索引假脱机这个怪东西啊
我的语句根本没有排序,为什么执行计划会对ERP_Order.OrderID进行排序呢top本来就需要先排序，然后再选择，比如很多操作如distinct，group by，在没有聚集索引的情况下都可能引起排序。2000的算法没有2008的好，比如where中多个条件时，2000必须按照复合索引中列的顺序来查才高效，否则就会扫描，但是2008开始，where中的顺序就不同了。并且从2005开始对tempdb的使用越来越多，索引假脱机按照我的经历，就是没有索引，但是关联时候又要排序，所以放到tempdb进行排序后再关联，tempdb这时候就成为了瓶颈。2000没怎么研究，不发表意见。总的来说，还是缺少索引的问题，你试试在2008中，临时表建个索引再用你top 1那句来执行看看会不会快一点

from TempTable where isnull(EndStockNumber,0)<>0'
这个表将会行扫瞄，建了索引也没有用的，因为用了isnull
所以楼主以后设计表时尽量采用默认值，让字段不为空从而避免NULL的出现

http://bbs.csdn.net/topics/390632722
你看看我的帖子，我的升级已经失败了搞了5天，重新弄成了sql 2000，要再次升级的话要重新写一遍或者重新过一遍数据库和存储过程。

没带order by子句的select top 1 ERP_OrderList.SalePrice ...
跟select max(ERP_OrderList.SalePrice) ...的结果是不一样的吧.
逻辑不一致,没有可比性.

你没有order by的情况下，top 1只是找到最上面那条，max是最大的值，如果max字段有索引，速度会快

其实楼主的最大疑问是，为什么升级之后，反而更加慢了。其实在升级之前，必须要做一些准备工作，比如，通过sql server的升级向导，看一下，原来库中的代码的兼容性等等。

同一个语句还是把top 1改成max之后的？

神奇啊,改用max()就正常了
使用top 1 查了一下执行计划,是由于查询计划使用了一个索引假脱机和排序造成花费了50%以上的时间
实际是花费了90%以上的时间在索引假脱机上,百度了一下这个索引假脱机,很多人都遇到过
有什么办法可以避免这个发生啊在2000使用top 1 和 max()是没区别的,一样快,为什么到2008就出现索引假脱机这个怪东西啊
我的语句根本没有排序,为什么执行计划会对ERP_Order.OrderID进行排序呢top本来就需要先排序，然后再选择，比如很多操作如distinct，group by，在没有聚集索引的情况下都可能引起排序。2000的算法没有2008的好，比如where中多个条件时，2000必须按照复合索引中列的顺序来查才高效，否则就会扫描，但是2008开始，where中的顺序就不同了。并且从2005开始对tempdb的使用越来越多，索引假脱机按照我的经历，就是没有索引，但是关联时候又要排序，所以放到tempdb进行排序后再关联，tempdb这时候就成为了瓶颈。2000没怎么研究，不发表意见。总的来说，还是缺少索引的问题，你试试在2008中，临时表建个索引再用你top 1那句来执行看看会不会快一点distinct，group by会排序这个很好理解,但top 1是不需要排序就可以找到的啊,能解析一下为什么top需要排序吗?
而且我这个查询语句where里面用到的条件字段我全部做到索引覆盖的了
2008对临时表的使用频繁同感啊,而且还莫名其妙的对一些临时表增加索引和更新统计信息
使用SQL Server Profiler会跟踪到很多系统增加的更新统计信息的语句,都是针对临时表的

同一个语句,都是使用top 1 的计划,如果2008改成使用max()的话,计划就和2000差不多了
索引假脱机不见了

如果没有聚集索引，TOP 1就会查询第一条插入的数据，如果有，就查出聚集索引创建时键值顺序的第一条。top 不一定会排序，但是经常会存在排序操作。
2008创建索引在临时表之后也会创建统计信息，这点和表变量不同。

还有你的执行计划，join的算法都不同，每个算法有一定条件下才有效

没用的,就算我怎么建索引,都不会快,难不成我要对ERP_OrderList.SalePrice建索引,没必要吧
索引一般都是对where里面用到的字段建吧,我现在可是索引全覆盖的啊,为什么还说我缺少索引呢没用的,因为扫描TempTable很快的,就1万条记录左右,就算我对TempTable增加索引也没用,一样慢但我的理解是max()是要排序的,你不排序是不可能找到最大值的,但top 1是不需要排序的
我只需要找到符合条件的一个就行了,按理是不需要排序的啊,请指教为什么top 需要排序呢我试过增加order by 但一样慢,请问order by 是对那个字段排序呢?ERP_OrderList.SalePrice吗?
如果是的话,试过没用的,一样慢,如果不是的话,我要对那个字段order by 才不会造成索引假脱机呢?
现在就在测试啦,呵呵,多谢鼎力支持,你帮助不少,谢啦,现在错误基本解决了,就差调优了

1、不是建了索引就有用，几千万的数据，如果选择性不高，索引无效。
2、忘了跟你说，top1只会返回一行，而max可能返回多行。
3、如果你只需要返回存在与否，直接exists（select 1）就可以了，没必要搞top，虽然看过有top和没有的效率没啥区别，但是还是不建议加些没用的东西。

SELECT  * ,
        ( SELECT TOP 1
                    ERP_OrderList.SalePrice
          FROM      ERP_Order WITH ( NOLOCK )
                    INNER JOIN ERP_OrderList WITH ( NOLOCK ) ON ERP_Order.OrderID = ERP_OrderList.OrderID
          WHERE     ERP_Order.FunctionID = 228
                    AND ERP_Order.OrderOddNumber = TempTable.WareStockFree1
                    AND ERP_OrderList.WareID = TempTable.WareID
                    AND ERP_OrderList.OrderListFree4 = TempTable.CheckOddNumber
        ) AS 销售单价
FROM    TempTable
WHERE   ISNULL(EndStockNumber, 0) <> 0你看看在2008的执行计划中是不是有丢失索引

没用的,就算我怎么建索引,都不会快,难不成我要对ERP_OrderList.SalePrice建索引,没必要吧
索引一般都是对where里面用到的字段建吧,我现在可是索引全覆盖的啊,为什么还说我缺少索引呢没用的,因为扫描TempTable很快的,就1万条记录左右,就算我对TempTable增加索引也没用,一样慢但我的理解是max()是要排序的,你不排序是不可能找到最大值的,但top 1是不需要排序的
我只需要找到符合条件的一个就行了,按理是不需要排序的啊,请指教为什么top 需要排序呢我试过增加order by 但一样慢,请问order by 是对那个字段排序呢?ERP_OrderList.SalePrice吗?
如果是的话,试过没用的,一样慢,如果不是的话,我要对那个字段order by 才不会造成索引假脱机呢?
现在就在测试啦,呵呵,多谢鼎力支持,你帮助不少,谢啦,现在错误基本解决了,就差调优了对了，看了刚才你贴处理的执行计划对比，发现2008的执行计划中，最右边表 ERP_OrderList，走的是“聚集索引扫描”，那个索引是Pk_ERP_OrderList_... ,然后上一个2000，对ERP_OrderList表，走的是索引IX_...你看看 2000中的ERP_OrderList 表有IX开头的索引不，还有2008中有这个索引吧，还是只有那个聚集索引。

再强调一点,我这两个子查询的表都有完善的索引的,聚集索引都有
where中使用到的字段都建了查询覆盖的了,不存在说索引不够的问题啊就是啊,我的一个存储过程执行过程很多更新临时表索引统计信息的语句执行,而且花费也不少
都是十毫秒级左右的,积少成多啊!!!我可是直接把语句复制过去执行的,一模一样,索引也是建的一模一样,至于为什么执行计划不同那就是
SQL Server的问题了,我就是要找出为什么2008的执行计划反而更差呢,怎么避免发生这样的问题

我的top是有用的啊,有返回内容的啊,怎么会没必要呢
数据量也不是很多,最大的表也不到200万行
max()会返回多行,不是吧,那么我改用max()不是会造成有可能错误???
返回多行是中间过程吧,最后还是会选择一个吧没有提示丢失索引,只是显示索引假脱机,不知道这算不算你说的丢失索引了
没用的,就算我怎么建索引,都不会快,难不成我要对ERP_OrderList.SalePrice建索引,没必要吧
索引一般都是对where里面用到的字段建吧,我现在可是索引全覆盖的啊,为什么还说我缺少索引呢没用的,因为扫描TempTable很快的,就1万条记录左右,就算我对TempTable增加索引也没用,一样慢但我的理解是max()是要排序的,你不排序是不可能找到最大值的,但top 1是不需要排序的
我只需要找到符合条件的一个就行了,按理是不需要排序的啊,请指教为什么top 需要排序呢我试过增加order by 但一样慢,请问order by 是对那个字段排序呢?ERP_OrderList.SalePrice吗?
如果是的话,试过没用的,一样慢,如果不是的话,我要对那个字段order by 才不会造成索引假脱机呢?
现在就在测试啦,呵呵,多谢鼎力支持,你帮助不少,谢啦,现在错误基本解决了,就差调优了对了，看了刚才你贴处理的执行计划对比，发现2008的执行计划中，最右边表 ERP_OrderList，走的是“聚集索引扫描”，那个索引是Pk_ERP_OrderList_... ,然后上一个2000，对ERP_OrderList表，走的是索引IX_...你看看 2000中的ERP_OrderList 表有IX开头的索引不，还有2008中有这个索引吧，还是只有那个聚集索引。
索引是一模一样的,至于为什么计划不同现在就是问题所在啊

我的top是有用的啊,有返回内容的啊,怎么会没必要呢
数据量也不是很多,最大的表也不到200万行
max()会返回多行,不是吧,那么我改用max()不是会造成有可能错误???
返回多行是中间过程吧,最后还是会选择一个吧没有提示丢失索引,只是显示索引假脱机,不知道这算不算你说的丢失索引了
没用的,就算我怎么建索引,都不会快,难不成我要对ERP_OrderList.SalePrice建索引,没必要吧
索引一般都是对where里面用到的字段建吧,我现在可是索引全覆盖的啊,为什么还说我缺少索引呢没用的,因为扫描TempTable很快的,就1万条记录左右,就算我对TempTable增加索引也没用,一样慢但我的理解是max()是要排序的,你不排序是不可能找到最大值的,但top 1是不需要排序的
我只需要找到符合条件的一个就行了,按理是不需要排序的啊,请指教为什么top 需要排序呢我试过增加order by 但一样慢,请问order by 是对那个字段排序呢?ERP_OrderList.SalePrice吗?
如果是的话,试过没用的,一样慢,如果不是的话,我要对那个字段order by 才不会造成索引假脱机呢?
现在就在测试啦,呵呵,多谢鼎力支持,你帮助不少,谢啦,现在错误基本解决了,就差调优了对了，看了刚才你贴处理的执行计划对比，发现2008的执行计划中，最右边表 ERP_OrderList，走的是“聚集索引扫描”，那个索引是Pk_ERP_OrderList_... ,然后上一个2000，对ERP_OrderList表，走的是索引IX_...你看看 2000中的ERP_OrderList 表有IX开头的索引不，还有2008中有这个索引吧，还是只有那个聚集索引。
索引是一模一样的,至于为什么计划不同现在就是问题所在啊你这一大串还真不好看哪些是你的问题，先说max返回多列，抛开你的数据，假设一个表上有1~10的数据，而且10有2条，你max的时候，单纯max是没问题，如果把其他字段带出来，那就可能出现2条。并且你没有order by额top，跟max其实没有绝对的等于关系，只能靠运气了。

改成这样呢，在2008中试试：exec('select *
,(select top 1 ERP_OrderList.SalePrice
  from ERP_Order with(nolock)
  inner loop join ERP_OrderList with(nolock)
          on ERP_Order.OrderID=ERP_OrderList.OrderID
  where ERP_Order.FunctionID=228 and
        ERP_Order.OrderOddNumber=TempTable.WareStockFree1 and
        ERP_OrderList.WareID=TempTable.WareID and
        ERP_OrderList.OrderListFree4=TempTable.CheckOddNumber
  ) as 销售单价from TempTable
where isnull(EndStockNumber,0)<>0'
)

erp_orderList.orderlistfree4和temptable.checkoddnumber这两个列上创建一个非聚集索引，我猜索引假脱机应该会消失

2008和2000的算法肯定不同，比如2005之后对a like '%xxx%'这种查询已经有了比较大的改进，不再是所谓的完全不走索引，2005如此，2008更不用说了。我觉得优化的话，应该先检查你的查询是否还有改进的部分，如果没有，再考虑索引问题。丢失索引会在执行计划图形化界面中有一行绿色的字，不过有些不会显示

刚才上面的那个是，把inner join 改成了 nested loop，
下面是改成这样，强制使用索引 ERP_OrderList with(nolock,index (ERP_OrderList的那个IX开头的索引))：exec('select *
,(select top 1 ERP_OrderList.SalePrice
  from ERP_Order with(nolock)
  inner join ERP_OrderList with(nolock,index (ERP_OrderList的那个IX开头的索引))
          on ERP_Order.OrderID=ERP_OrderList.OrderID
  where ERP_Order.FunctionID=228 and
        ERP_Order.OrderOddNumber=TempTable.WareStockFree1 and
        ERP_OrderList.WareID=TempTable.WareID and
        ERP_OrderList.OrderListFree4=TempTable.CheckOddNumber
  ) as 销售单价from TempTable
where isnull(EndStockNumber,0)<>0'
)

索引的建法我问题描述那里讲得很清楚啦,都是最优建法的了,表的数据量也只有区区200万,如果200万都有问题
那么我对SQL Server 2008就很失望了,如何和Oracle竞争啊,遇到千万级,亿级以上表那怎么办呢
百万级别的查询在实际生产很正常的啊,经常发生的

还有你的查询是怎么查？直接展示？我试过同样的一台服务器，2000的，用2000的查询分析器查一个语句，很快，但是用2005的SSMS再执行，就很慢，这个主要在数据展示的层面，数据其实已经查询出来了

你这一大串还真不好看哪些是你的问题，先说max返回多列，抛开你的数据，假设一个表上有1~10的数据，而且10有2条，你max的时候，单纯max是没问题，如果把其他字段带出来，那就可能出现2条。并且你没有order by额top，跟max其实没有绝对的等于关系，只能靠运气了。
还是不太明白你的意思,按你的意思,是不是说如果我执行
select max(...) from ... 有可能返回多于一行的结果呢
如果真的话那就很危险了,因为我很多子查询都这样写的哦,如果返回多于一行那么就会发生错误了
怎么可能返回多于一行呢,能给出实际例子吗

哥哥们，都不管他语句么，这种全表扫的用子查询当然会慢了。exec ('select t1.*
,t2.SalePrice as 销售单价
from TempTable t1
left join (
select OrderOddNumber,WareID,OrderListFree4,max(SalePrice) as SalePrice
from ERP_Order with(nolock)
inner join ERP_OrderList with(nolock) on ERP_Order.OrderID=ERP_OrderList.OrderID
and ERP_Order.FunctionID=228
group by OrderOddNumber,WareID,OrderListFree4
) t2 on t1.WareStockFree1=t2.OrderOddNumber and t1.WareID=t2.WareID and t1.CheckOddNumber=t2.OrderListFree4
where isnull(t1.EndStockNumber,0)<>0')用这个试试。

select max(...) from ... 不会返回，不过select max(...) ,其他列 from ... 这个就会有可能

先拿你2000的执行计划来说：book lookup，这个最常见的可能是，某个查询使用了非聚集索引，但是select中有些列不包含在这个索引里面，所以需要额外去找聚集索引拿哪些不包含在非聚集索引中的数据，这就是其中一个连2000的时候都已经不够合理的地方，也可以说，实际上还没有真正的索引覆盖

这个我也注意到了,在查询分析器执行的话,他是执行完毕一次过显示返回结果的
但如果在SSMS执行的话,他会不断显示结果,如果结果有1万行的话,他居然可以逐几百行的那样添加显示在表格回到我的问题,应该不关这个事,因为我改用max()后2008的执行速度就高于2000了,返回结果的行数是一样的

好像还没贴出max的执行计划吧？贴来看看，2000的就不用了，你贴过了，我想看看top 1和max的执行计划差异

try this,select c.*,max(b.SalePrice) '销售单价'
from ERP_Order(nolock) a
inner join ERP_OrderList(nolock) b on a.OrderID=b.OrderID
inner join (select * from TempTable(nolock)
where isnull(EndStockNumber,0)<>0) c
on a.OrderOddNumber=c.WareStockFree1 and b.WareID=c.WareID and b.OrderListFree4=c.CheckOddNumber
where a.FunctionID=228

这个就不怕了,因为如果你在其中一个字段使用了集合函数的话,你其他字段必须也使用聚合函数或者其他字段必须在Group by 出现的,不然就会出错,这个不是问题的

这个就不怕了,因为如果你在其中一个字段使用了集合函数的话,你其他字段必须也使用聚合函数或者其他字段必须在Group by 出现的,不然就会出错,这个不是问题的我只是想说明TOP 和max并不总是等价而已。吃饭前，看别人有啥建议。另外40楼的你也试试

sorry! 试试这个,select c.WareStockFree1,c.WareID,c.CheckOddNumber,max(b.SalePrice) 'SalePrice'
from ERP_Order(nolock) a
inner join ERP_OrderList(nolock) b on a.OrderID=b.OrderID
inner join (select * from TempTable(nolock)
where isnull(EndStockNumber,0)<>0) c
on a.OrderOddNumber=c.WareStockFree1 and b.WareID=c.WareID and b.OrderListFree4=c.CheckOddNumber
where a.FunctionID=228
group by c.WareStockFree1,c.WareID,c.CheckOddNumber

调试易

同样一个查询2008比2000慢3倍以上啊???

解决方案 »