|
最近有项目反应，在服务器CPU使用较高的时候，我们的事件查询页面非常的慢，查询几条记录竟然要4分钟甚至更长，而且在翻第二页的时候也是要这么多的时间，这肯定是不能接受的，也是让现场用SQLServerProfiler把语句抓取了上来。
用ROW_NUMBER()进行分页
我们看看现场抓上来的分页语句：
view source
print?
select top 20 a.*,ag.Name as AgentServerName,,d.Name as MgrObjTypeName,l.UserName asuserName
from eventlog as a
left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm
left join addrnode as c on b.AddrId=c.Id
left join mgrobjtype as d on b.MgrObjTypeId=d.Id
left join eventdir as e on a.EventBm=e.Bm
left join agentserver as ag on a.AgentBm=ag.AgentBm
left join loginUser as l on a.cfmoper=l.loginGuid
where a.OrderNo not in (
select top 0 OrderNo
from eventlog as a
left join mgrobj as b on a.MgrObjId=b.Id
left join addrnode as c on b.AddrId=c.Id
where 1=1 and a.AlarmTime>='2014-12-01 00:00:00' and a.AlarmTime='2014-12-01 00:00:00' and a.AlarmTime='2014-12-01 00:00:00' and a.AlarmTime='2011-12-01 00:00:00' and a.AlarmTime'2011-12-01 00:00:00' AND AlarmTime= '2011-12-01 00:00:00' AND AlarmTime |