全部

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

maotin 2003-03-31 01:46:41

我有一个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条记录，

...全文

9 点赞收藏 12

写回复

12 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

ibanez550 2003-03-31

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

回复赞

CrazyFor 2003-03-31

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

看一下哪步最耗时。

回复赞

maotin 2003-03-31

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

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

回复赞

benxie 2003-03-31

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

回复赞

maotin 2003-03-31

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

回复赞

maotin 2003-03-31

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

回复赞

pengdali 2003-03-31

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

回复赞

zqllyh 2003-03-31

说快了

回复赞

maotin 2003-03-31

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

回复赞

CrazyFor 2003-03-31

是什么问题？

回复赞

zqllyh 2003-03-31

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

回复赞

maotin 2003-03-31

当我用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秒的时间，请各位帮我看看是什么原因？谢谢

回复赞