一sql语句性能问题--在线等
当执行这条语句的时候，执行时间大概在3秒。
SELECT ObjCampaign.ObjCampaignId,SUBSTRING(MediaSpotMatch.YEARMONTH,1,4) as [Year],ObjCampaign.CampaignTargetId,
SUM(CASE WHEN MediaSpotRating.Universe = 0 THEN 0 ELSE MediaSpotRating.Impression/CONVERT(DECIMAL(38,3),MediaSpotRating.Universe) END) * 110 AS [Rating_11_0]
FROM MediaSpotMatch
Inner join MediaSpotRating On MediaSpotMatch.MediaSpotMatchID = MediaSpotRating.MediaSpotMatchId
INNER JOIN dbo.ObjCampaign ON ObjCampaign.ObjCampaignId = MediaSpotMatch.ObjCampaignId
INNER JOIN #TMP_Campaign ON MediaSpotMatch.ObjCampaignId = #TMP_Campaign.ObjCampaignId
Where MediaSpotMatch.YearMonth >= '200710' AND MediaSpotMatch.YearMonth <= '200712' AND MediaSpotRating.YearMonth >= '200710'
AND MediaSpotRating.YearMonth <= '200712' AND MediaSpotRating.TargetId = 11
AND MediaSpotMatch.ActualSpotStartDate >= '2007-10-01' AND MediaSpotMatch.ActualSpotStartDate <= '2007-12-31'
Group By ObjCampaign.ObjCampaignId,SUBSTRING(MediaSpotMatch.YEARMONTH,1,4),ObjCampaign.CampaignTargetId
但是当我把sql 语句中的日期都换成11月份，也就是下面的语句的时候，执行时间大概在2分钟左右。
SELECT ObjCampaign.ObjCampaignId,SUBSTRING(MediaSpotMatch.YEARMONTH,1,4) as [Year],ObjCampaign.CampaignTargetId,
SUM(CASE WHEN MediaSpotRating.Universe = 0 THEN 0 ELSE MediaSpotRating.Impression/CONVERT(DECIMAL(38,3),MediaSpotRating.Universe) END) * 110 AS [Rating_11_0]
FROM MediaSpotMatch
Inner join MediaSpotRating On MediaSpotMatch.MediaSpotMatchID = MediaSpotRating.MediaSpotMatchId
INNER JOIN dbo.ObjCampaign ON ObjCampaign.ObjCampaignId = MediaSpotMatch.ObjCampaignId
INNER JOIN #TMP_Campaign ON MediaSpotMatch.ObjCampaignId = #TMP_Campaign.ObjCampaignId
Where MediaSpotMatch.YearMonth >= '200711' AND MediaSpotMatch.YearMonth <= '200712' AND MediaSpotRating.YearMonth >= '200711'
AND MediaSpotRating.YearMonth <= '200712' AND MediaSpotRating.TargetId = 11
AND MediaSpotMatch.ActualSpotStartDate >= '2007-11-01' AND MediaSpotMatch.ActualSpotStartDate <= '2007-12-31'
Group By ObjCampaign.ObjCampaignId,SUBSTRING(MediaSpotMatch.YEARMONTH,1,4),ObjCampaign.CampaignTargetId
请问这样的结果可能是什么原因造成的？
[解决办法]
这样其实是很正常的啊。
使用不同的限制条件检索数据，用的时间一般在绝对意义上是肯定又区别的。
他们得到的结果集不同，显示出来就是一个从磁盘到内存的读取过程（很消耗资源的IO操作），应该说这部分差异是造成执行时间的主要原因。
[解决办法]
查看一下系统资料占用率是多少？--执行语句1，语句2时
--------
用事件探查器追一下原因，估计有其它事务锁住了表
[解决办法]
原因很可能是缓存，你多执行几次后面的语句，如果时间有减少就说明是缓存的原因
[解决办法]
检查各个连接条件的字段上有没有索引
[解决办法]
多次执行都是2分钟？
看一下执行计划，对比两个语句，看成本消耗的差别在什么地方
[解决办法]
两个语句分别返回多少行数据?
