查询优化的问题

chenhaoying 2011-07-22 09:51:11

有个View_MemberClearingInfo视图由多个表级联起来：
SELECT dbo.MemberPointsClearing.MPCId, dbo.MemberPointsClearing.MemberCode, dbo.MemberPointsClearing.Mobile,
dbo.MemberPointsClearing.ClearingStandard, dbo.MemberPointsClearing.Amount, dbo.MemberPointsClearing.CMaker,
dbo.MemberPointsClearing.CDate, dbo.MemberPointsClearing.Checker, dbo.MemberPointsClearing.CheckDate, dbo.MemberPointsClearing.PointTotal,
dbo.MemberPointsClearing.ClearingSerialNumber, dbo.MemberPointsClearing.Remark, dbo.Member.MemberName, dbo.TicketsProject.TPName,
dbo.TicketsProject.TPType, dbo.MemberPointsClearingMx.SaleDate, dbo.TicketSaleMx.SaleMXSaleCode, dbo.MemberPointsClearingMx.Adult,
dbo.MemberPointsClearingMx.AdultPoints,
dbo.MemberPointsClearing.ClearingStandard * dbo.MemberPointsClearingMx.AdultPoints AS RebateTotalPrice
FROM dbo.MemberPointsClearing INNER JOIN
dbo.MemberPointsClearingMx ON dbo.MemberPointsClearing.MPCId = dbo.MemberPointsClearingMx.MPCId LEFT OUTER JOIN
dbo.Member ON dbo.MemberPointsClearing.MemberCode = dbo.Member.MemberCode INNER JOIN
dbo.TicketsProject ON dbo.MemberPointsClearingMx.TPCode = dbo.TicketsProject.TPCode INNER JOIN
dbo.TicketSaleMx ON dbo.MemberPointsClearingMx.SaleMxId = dbo.TicketSaleMx.id

在查询分析器中使用查询：SELECT TOP 20 * FROM [View_MemberClearingInfo]
WHERE CDate>='2011-06-01'
and CDate<='2011-07-20'
ORDER BY MCId DESC
需要6分钟才能出结果，而把CDate>='2011-06-01' 改为CDate>='2011-05-01' ，也就是把时间范围放大了，数据量更多了，查询的速度却变得才1秒就出结果了。我检查了CDate字段都没什么异常的（说明一点Cdate字段的值是getdate()默认填的），为什么出现这种情况？该如何解决啊。
我把日期改到其他范围也是0秒～5秒就出来了，也就是说这个时间起点有几个值让我执行很慢啊。

...全文

58 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

chenhaoying 2011-07-22

打赏
举报

找到原因了，有个级联用的是左级联，若右边的没有值速度就很慢。我把视图中的dbo.Member.MemberName改为
(SELECT TOP (1) MemberName
FROM dbo.Member
WHERE (MemberCode = dbo.MemberClearing.MemberCode)) AS MemberName
速度马上恢复正常。