有个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.Re, 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秒就出来了,也就是说这个时间起点有几个值让我执行很慢啊。
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.Re, 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秒就出来了,也就是说这个时间起点有几个值让我执行很慢啊。
解决方案 »
- 数据库中怎么查最后几行数据
- 哪位高人有Log Explorer
- 跪求高手帮助,代理快照点启动后无反应
- 我用程序登陆本机(或者局域网内)上的SQL Server2000时用机器名和Localhost可以登陆.用IP地址不可以登陆是什么原因呢?
- SQL查询问题,想了N天了,高手进来帮一下忙
- 求一可以历遍每个数据表的存储过程,各位高手请进!
- 有什么办法定期的把select出的数据输出到excel表里?是不是用dts包?
- delphi 编的程序,连接的数据库是sql server,连接数据库后,delphi执行存储过程时出错
- 关于Windows XP的IIS与SQL SERVER安装的问题
- 求大神解答一条sql
- 【sql】日期格式比较,求解!
- 求一简单的sql语句
DBCC 下表
重建下索引,统计信息看看。
(SELECT TOP (1) MemberName
FROM dbo.Member
WHERE (MemberCode = dbo.MemberClearing.MemberCode)) AS MemberName
速度马上恢复正常。