请教各位一个很奇怪的问题，关于TOP

[maotin]

我有一个sql语句：
SELECT OrderDetail.Price,
OrderDetail.Vat,
OrderDetail.ArticleId,
Article.ProfitCenter,
Article.ArticleName,
OrderDetail.OrderDetailId
FROM OrderDetail, Address,Payment, [Order], Article
WHERE
Payment.OrderId = [Order].OrderId AND
OrderDetail.OrderId = Payment.OrderId AND
OrderDetail.ArticleId = Article.ArticleId AND
Payment.CustomerId = [Order].CustomerId AND
Payment.PaymethodId = 90 AND
Payment.Paydate <= '2003-03-31' AND
Payment.Paydate >= '2002-02-02' AND
OrderDetail.OrderDetailId > 0 AND
Address.CountryId = 1 AND
OrderDetail.DeliveryAddressId = AddressId
ORDER BY OrderDetail.OrderDetailId

我执行用了34秒，取出了10859条记录，

[maotin]

当我用Top时：
SELECT TOP 1000
OrderDetail.Price,
OrderDetail.Vat,
OrderDetail.ArticleId,
Article.ProfitCenter,
Article.ArticleName,
OrderDetail.OrderDetailId
FROM OrderDetail, Address,Payment, [Order], Article
WHERE
Payment.OrderId = [Order].OrderId AND
OrderDetail.OrderId = Payment.OrderId AND
OrderDetail.ArticleId = Article.ArticleId AND
Payment.CustomerId = [Order].CustomerId AND
Payment.PaymethodId = 90 AND
Payment.Paydate <= '2003-03-31' AND
Payment.Paydate >= '2002-02-02' AND
OrderDetail.OrderDetailId > 0 AND
Address.CountryId = 1 AND
OrderDetail.DeliveryAddressId = AddressId
ORDER BY OrderDetail.OrderDetailId

我居然要用 2分30秒的时间， 请各位帮我看看是什么原因？ 谢谢

[zqllyh]

搞清楚连接关系先，这个被揣成INNER　JOIN

[CrazyFor]

是什么问题？

[maotin]

To zqllyh(学习Stupid As Pig中...) :
能不能说详细一点。谢谢。

[zqllyh]

说快了

[pengdali]

try:


select top 1000 * (
SELECT OrderDetail.Price,
OrderDetail.Vat,
OrderDetail.ArticleId,
Article.ProfitCenter,
Article.ArticleName,
OrderDetail.OrderDetailId
FROM OrderDetail, Address,Payment, [Order], Article
WHERE
Payment.OrderId = [Order].OrderId AND
OrderDetail.OrderId = Payment.OrderId AND
OrderDetail.ArticleId = Article.ArticleId AND
Payment.CustomerId = [Order].CustomerId AND
Payment.PaymethodId = 90 AND
Payment.Paydate <= '2003-03-31' AND
Payment.Paydate >= '2002-02-02' AND
OrderDetail.OrderDetailId > 0 AND
Address.CountryId = 1 AND
OrderDetail.DeliveryAddressId = AddressId) tem
ORDER BY OrderDetailId

[maotin]

To CrazyFor(蚂蚁) ：
我用了Top 1000，为什么执行的时间反而要比不用Top要慢很多？？

[maotin]

To pengdali(大力) :
为什么我那样用法，会出现这个问题？

[benxie]

不知道是不是，你在后面有order by 的原因。
学习。

[maotin]

To benxie(峰之枫):
呵呵，好像是order by的原因，能不能解说一下。

请各位高手解释一下原因。谢谢，找到原因马上揭帖。

[CrazyFor]

把语句拷贝到查询分析器，按“显示预计执行计划”（CTRL+L）

看一下哪步最耗时。

[ibanez550]

带top关键字指针定位消耗时间跟你的记录数很关吧
你可以试一下top 10条记录需要多少时间，估计很少
以上只是猜测，关注