海量数据查询分页优化

[xpmysky]

数据量非常大，存储都是按T来计算。现在只做查询显示，查询条件比较多。在做分页显示的时候比较慢。已经合理的使用了索引。不知道各位有没有好的思路或者方法。或者能对下面的SQL语句能在优化一下。
select * from (select b.*,a.*,rownumber() over(order by a.LAST_MODIFIED_TIME ASC) as co from TEC_T_EVT_REP
as a join TEC_T_SLOTS_EVT b on a.DATE_RECEPTION=b.DATE_RECEPTION and a.SERVER_HNDL= b.SERVER_HNDL and a.EVENT_HNDL=b.EVENT_HNDL
where a.LAST_MODIFIED_TIME>='2008-05-20 00:00:00'and a.LAST_MODIFIED_TIME>='2008-05-27 23:59:59' and a.class NOT IN
('NIDS_Sensor_Event','NIDS_Alert_Event', 'TrustLink_Event2','NIDS_Network_Event','TrustLink_Event','NIDS_Event') AND(a.severity >= 30) and b.name="" //条件比较多
)as o where o.co between 21 and 30;

[xpmysky]

select c,c1 from (select b.SLOT_NAME as c,b.SHORT_SLOT_VALUE as c1,rownumber() over(order by a.LAST_MODIFIED_TIME ASC) as co from TEC_T_EVT_REP
as a join TEC_T_SLOTS_EVT b on a.DATE_RECEPTION=b.DATE_RECEPTION and a.SERVER_HNDL= b.SERVER_HNDL and a.EVENT_HNDL=b.EVENT_HNDL
where a.LAST_MODIFIED_TIME>='2008-05-20 00:00:00'and a.LAST_MODIFIED_TIME>='2008-05-27 23:59:59' and a.class NOT IN
('NIDS_Sensor_Event','NIDS_Alert_Event', 'TrustLink_Event2','NIDS_Network_Event','TrustLink_Event','NIDS_Event')and b.SLOT_NAME in ('ClientPort','name') and b.SHORT_SLOT_VALUE in('','')//in里面有很多条件
)as o where o.co between 21 and 30;


希望高手能够进一步优化上面这条SQL语句

[xpmysky]

没有朋友帮忙优化一下吗？

[哥是我]

TEC_T_EVT_REP 是主表 把， 先 吧TEC_T_EVT_REP 查询出 试试看，

见 ：
select * from (select b.*,a.*,rownumber() over(order by a.LAST_MODIFIED_TIME ASC) as co
from (
select * from //
TEC_T_EVT_REP
where a.LAST_MODIFIED_TIME>='2008-05-20 00:00:00'
and a.LAST_MODIFIED_TIME>='2008-05-27 23:59:59'
and a.class NOT IN
('NIDS_Sensor_Event','NIDS_Alert_Event', 'TrustLink_Event2','NIDS_Network_Event','TrustLink_Event','NIDS_Event')
AND(a.severity >= 30 )
) as a join TEC_T_SLOTS_EVT b
on a.DATE_RECEPTION=b.DATE_RECEPTION
and a.SERVER_HNDL= b.SERVER_HNDL
and a.EVENT_HNDL=b.EVENT_HNDL
where
b.name="" //条件比较多
)as o where o.co between 21 and 30;

[xpmysky]

按照你的方法速度没有很大的提升~因为数据太多。查询短时间内比较快。如果查询一个星期或者一个月就非常的慢~
希望能有好的建议~~