请教各位一个很奇怪的问题,关于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秒的时间, 请各位帮我看看是什么原因? 谢谢

回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2003-03-31 01:46
社区公告
暂无公告