SQL server分页查询效率低导致web后台增删改问题

问题描述：web后台增删改操作很卡，卡的时间长达1个小时左右

 

环境工具：数据库SQL  server；web服务器apache+tomcat

 

解决过程：1.  网站上线后，使用后台的编辑人员发现在后台维护数据很卡，如图（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';

发现好像是进程死锁了，结果如图（2）。于是在网上寻找解决死锁的方法，始终不得要领。

图（2）

 

        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;