同样的SQL,同样的库,WHERE条件时间点不一样,速度差异巨大
以下两段sql只有where 条件中的updatetime范围不一样,第一段非常快后一段很慢 为什么呢? 并且后一段的updatetime在第一段的范围内!求高人解答!
set statistics io on
declare @PageIndex int
declare @PageSize int
set @PageIndex=1
set @PageSize=10000
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY E.FormCode asc) AS ROWNUM ,E.FormCode
FROM OrderForm(NOLOCK) E
WHERE Status in(-6,-5,24,25)
and '2007/2/7 15:59:50'<=updateTime and updateTime<= '2012/2/9 15:59:50'
)TempTable
WHERE ROWNUM BETWEEN(@PageIndex-1)*@PageSize+1 AND @PageIndex*@PageSize
----------------------------
set statistics io on
declare @PageIndex int
declare @PageSize int
set @PageIndex=1
set @PageSize=100
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY E.FormCode asc) AS ROWNUM
,E.FormCode
FROM OrderForm(NOLOCK) E
WHERE Status in(-6,-5,24,25)
and '2011/2/7 15:59:50'<=updateTime and updateTime<= '2011/2/11 15:59:50'
)TempTable
WHERE ROWNUM BETWEEN(@PageIndex-1)*@PageSize+1 AND @PageIndex*@PageSize