SQL server分页查询效率低导致web后台增删改问题
问题描述：web后台增删改操作很卡，卡的时间长达1个小时左右
?
问题环境：数据库SQL? server；web服务器apache+tomcat
?
问题调查：1.? 网站上线后，使用后台的编辑人员发现在后台维护数据很卡，如图（1），我先检查apache和tomcat发现正常，然后怀疑是数据库的性能问题，就重启了下数据库服务，卡的情况得到解决。
?
????????????????? 2.? 我请教有经验的人，有人说可能是数据库服务器的磁盘阵列是raid5，raid5对SQL server的数据库写操作的性能有很大影响，尤其是数据量大的时候，我通过重装SQL server数据库等各种方式验证，发现不是。
?
??????????????????3.? 问题再次发生时，我想重启数据库服务器可以解决问题，还可以从web服务器的角度考虑一下，于是重启web服务器，发现也可以解决问题。
?
??????????????????4.? 根据以上情况，我判断应该是web服务访问数据库服务，处理SQL的session发生异常，于是使用
select request_owner_id
,request_owner_type
,resource_type
,resource_associated_entity_id
,request_session_id spid
,request_lifetime
,request_status
,request_type
,request_mode
,OBJECT_NAME(resource_associated_entity_id) tableName
from? sys.dm_tran_locks
where resource_type='OBJECT';
发现好像是进程死锁了。于是在网上寻找解决死锁的方法，始终不得要领。
?
????????5. 后来仔细研究死锁，想起死锁是两个以上的进程互掐或者环掐，我查到的结果只有一个进程，于是我用
select dtl.request_session_id spid
,spc.spid
,spc.kpid
,spc.blocked
,spc.lastwaittype
,spc.waitresource
,spc.dbid
,spc.uid
,spc.cpu
,spc.status
,spc.cmd
,spc.loginame
,dtl.request_lifetime
,dtl.request_status
,dtl.request_mode
,OBJECT_NAME(dtl.resource_associated_entity_id) tableName
from? sys.dm_tran_locks dtl,sys.sysprocesses spc
where dtl.request_session_id=spc.spid
and spc.loginame='cicrodb'
and dtl.resource_type='OBJECT';
才发现不是死锁，是进程阻塞。那么到底造成阻塞的这个进程到底在什么呢？
我用dbcc inputbuffer(77)找到正在阻塞的进程所执行的语句
select top 20 ci.pre_title,
ci.is_pic,
ci.info_id,
ci.cat_id,
ci.model_id,
ci.description,
ci.from_id,
ci.title,
ci.subtitle,
ci.title_color,
ci.thumb_url,
ci.author,
ci.editor,
ci.source,
ci.content_url,
ci.weight,
ci.hits,
ci.day_hits,
ci.week_hits,
ci.month_hits,
ci.released_dtime,
ci.site_id,
ci.page_count,
ca.cat_cname?????
from cs_info ci,cs_info_category ca???????
where ci.cat_id = ca.cat_id
and ci.site_id = ca.site_id???????????
and ca.cat_id ='10260'?????????
and ci.info_status = 8
and ci.final_status = 0????
and ci.info_id not in?????
(select top 111940 ci.info_id????
? from cs_info ci,cs_info_category ca???????
? where ci.cat_id = ca.cat_id
? and ci.site_id = ca.site_id????
? and ( ca.cat_id in ( select cat_id from cs_info_category where cat_position like '$0$10258$10260$%' ) )?????
? and ci.info_status = 8
? and ci.final_status = 0??????
? order by ci.released_dtime desc);
???????6. 这段SQL是一段分页查询的SQL，采用了top方案，我直接将这段SQL复制到SQL server客户端上执行，发现要1个小时才能执行完，于是印证了为什么客户保存数据，要卡1个多小时。因为目前SQL server分页查询有三种方案:
???????A.?top方案
?????? B. max/min方案
?????? C. rownumber方案
效率：rownumber方案?> max/min方案 > top方案；rownumber方案sql复杂，支持SQL server2000以上；max/min方案sql复杂，不支持非唯一性列排序查询；top方案不支持复合主键查询。
?
???????7. 根据项目的需要，选择rownumber方案，改为如下情况：
select top 20 ci.rownum,
ci.pre_title,
ci.is_pic,
ci.info_id,
ci.cat_id,
ci.model_id,
ci.description,
ci.from_id,
ci.title,
ci.subtitle,
ci.title_color,
ci.thumb_url,
ci.author,
ci.editor,
ci.source,
ci.content_url,
ci.weight,
ci.hits,
ci.day_hits,
ci.week_hits,
ci.month_hits,
ci.released_dtime,
ci.site_id,
ci.page_count,
ci.cat_cname?????
from (select top 111960 ROW_NUMBER() OVER (ORDER BY ci.info_id) rownum,
ci.pre_title,
ci.is_pic,
ci.info_id,
ci.cat_id,
ci.model_id,
ci.description,
ci.from_id,
ci.title,
ci.subtitle,
ci.title_color,
ci.thumb_url,
ci.author,
ci.editor,
ci.source,
ci.content_url,
ci.weight,
ci.hits,
ci.day_hits,
ci.week_hits,
ci.month_hits,
ci.released_dtime,
ci.site_id,
ci.page_count,
ca.cat_cname????
? from cs_info ci,cs_info_category ca???????
? where ci.cat_id = ca.cat_id
? and ci.site_id = ca.site_id????
? and ( ca.cat_id in ( select cat_id from cs_info_category where cat_position like '$0$10258$10260$%' ) )
? and ci.info_status = 8
? and ci.final_status = 0
order by ci.released_dtime desc) ci
where ci.rownum>111940
order by ci.released_dtime desc;?
????????????
