这句SQL存在性能问题，如何优化？

Question

以下这句SQL看起来不是很复杂，但是性能非常差，经常需要花费好几秒，请教怎么优化它?在一个大表上反复查询，发现时间耗在count(0) over ( ) as totalcount上面

select  a.inquiryid ,
         a.inquiryno ,
         a.inquirystatus ,
         a.requireid ,
         a.datachange_createtime ,
         a.datachange_lasttime ,
         a.creator ,
         a.ipaddress ,
         a.remark ,
         a.rejectreasonid ,
         a.inquirydetailid ,
         a.isdelete ,
         a.rejectreason ,
         a.departure ,
         a.destination ,
         a.platformcreator ,
         a.tourid ,
         a.nextcontacttime ,
         a.contactstatus ,    
         a.contactcreatetime as lastcontact_createtime,
         count(0) over ( ) as totalcount
    from prd_inquiry a ( nolock )        
        join  prd_inquirysupply c ( nolock ) on c.inquiryid = a.inquiryid 
        where   1 = 1  and c.inquirysupplystatus = @requirestatus 
        order by a.inquiryid desc  offset  ( @pageindex - 1 ) * @pagesize rows fetch next @pagesize rows only
        
            exec sp_executesql n'/*100000701*/
 select  a.inquiryid ,
                a.inquiryno ,
                a.inquirystatus ,
                a.requireid ,
                a.datachange_createtime ,
                a.datachange_lasttime ,
                a.creator ,
                a.ipaddress ,
                a.remark ,
                a.rejectreasonid ,
                a.inquirydetailid ,
                a.isdelete ,
                a.rejectreason ,
                a.departure ,
                a.destination ,
                a.platformcreator ,
                a.tourid ,
                a.nextcontacttime ,
                a.contactstatus ,    
                a.contactcreatetime as lastcontact_createtime,
                count(0) over ( ) as totalcount
        from    prd_inquiry a ( nolock )        
         join  prd_inquirysupply c ( nolock ) on c.inquiryid = a.inquiryid 
        where   1 = 1  and c.inquirysupplystatus = @requirestatus 
        order by a.inquiryid desc  offset  ( @pageindex - 1 ) * @pagesize rows fetch next @pagesize rows only ',n'@pageindex int,@pagesize int,@requirestatus int',@pageindex=1,@pagesize=50,@requirestatus=10

Answer 1

这个sql的过滤条件比较少，inquirysupplystatus过滤左右不明显。

如果inquiryid 是自增类型的数字，建议在查询中增加最大inquiryid 字段，并从页面传入到后续的sql语句中，这样就可以通过索引快速查询一个pagesize包含的记录，如：

select *, max(inquiryid) over () as max_inquiryid
from t
where inquiryid > @max_inquiryid 
order by a.inquiryid desc  offset 100 rows fetch next rows only