原语句:
select top 5 Id,ClassId,Title,Click,IsMsg,IsTop,IsRed,IsHot,IsSlide,
IsLock,AddTime,FontStyle,Color,ImgUrl from Article
where Id not in(select top 15 Id from Article
where IsLock=1 and Id>0 order by AddTime desc) and IsLock=1
and Id>0 order by AddTime desc
一万五千条数据执行一次需要十秒。
找到改进方法但不能加IsLock=1的参数
select top 15 Id,ClassId,Title,Click,IsMsg,IsTop,IsRed,IsHot,IsSlide,
IsLock,AddTime,FontStyle,Color,ImgUrl from Article
where (Id>(select max (id) from
(select top ((0+1)*15) id from article order by id) as T))
order by id
望高手给给予解决。
select top 5 Id,ClassId,Title,Click,IsMsg,IsTop,IsRed,IsHot,IsSlide,
IsLock,AddTime,FontStyle,Color,ImgUrl from Article
where Id not in(select top 15 Id from Article
where IsLock=1 and Id>0 order by AddTime desc) and IsLock=1
and Id>0 order by AddTime desc
一万五千条数据执行一次需要十秒。
找到改进方法但不能加IsLock=1的参数
select top 15 Id,ClassId,Title,Click,IsMsg,IsTop,IsRed,IsHot,IsSlide,
IsLock,AddTime,FontStyle,Color,ImgUrl from Article
where (Id>(select max (id) from
(select top ((0+1)*15) id from article order by id) as T))
order by id
望高手给给予解决。
select Id,ClassId,Title,Click,IsMsg,IsTop,IsRed,IsHot,IsSlide,
IsLock,AddTime,FontStyle,Color,ImgUrl
from(
select *,row_number()over(order by AddTime desc)rn from Article
where IsLock=1 and Id>0
)t where rn between 11 and 15
---------
在条件上加上索引试试
select top 15 Id,ClassId,Title,Click,IsMsg,IsTop,IsRed,IsHot,IsSlide,
IsLock,AddTime,FontStyle,Color,ImgUrl from Article
where islock=1 and (Id>(select max (id) from
(select top ((4+1)*15) id from article where IsLock=1 order by id) as T))
order by id
这个Islock在ID前和后原来执行的结果不一样。
from
(select top 20
Id,ClassId,Title,Click,IsMsg,IsTop,IsRed,IsHot,IsSlide,
IsLock,AddTime,FontStyle,Color,ImgUrl
from Article
where
IsLock=1 and Id>0 order by AddTime desc
)t
order by AddTime asc这样测测效率
IsLock,AddTime,FontStyle,Color,ImgUrl from Article
where islock=1 and (Id>(select max (id) from
(select top ((页码+1)*页大小) id from article where IsLock=1 order by id) as T))
order by id
只有这个方法可以,性能也很不错。