每天一问！多表复合查询分页效率问题！满意马上给分！欢迎各抒己见

[chenkui2008]

select  * from (
select  Row_number() over (order by ActivityInstanceID desc) as asrserialno,
* from(
select ActivityInstanceID,attachmentflag,PreUser,ActivityName,left(ProcessInstanceDesc,12) as ProcessInstanceDesc,
convert(varchar(4),accomplishrate)+'%' as complete2,
cast(completeRate/cast((isnull(RemainTime,0)-isnull(ActivityTime,0)*(isnull(AccomplishRate,0)/100)) as float) as numeric(10,2)) as rate2,
cast(cast(completeRate as float)/3600 as numeric(10,2)) as remaintime2,
convert(varchar(16),ActivityInstanceDeadline,120) as deadtime2,
convert(varchar(16),ActivityInstanceInitialTime,120) as arrivetime2,
ProcessInstanceWeight,DirectFinishFlag,ProcessInstanceId from (
select T.ActivityMostProbableDuration as ActivityTime, AI.ActivityInstanceID, AI.processinstanceid, 
AI.attachmentflag, AI.activitylongestduration, AI.ActivityInstanceDeadline,
AI.ActivityInstanceCompleteTime,dbo.F_WorkTimeCalculate(convert(varchar(19),getdate(),120),PI.Deadline,
AI.userid,AI.RoleID) as completeRate, AI.accomplishrate, AI.directfinishflag,AI.PreUser, AI.ActivityName,
PI.ProcessInstanceDesc, AI.ActivityInstanceInitialTime, AI.OverdueFlag, AI.ProcessInstanceWeight,
PI.Deadline, AI.RemainTime, AI.userid, AI.RoleID  
from ActivityInstanceList AI (nolock),
ProcessInstance PI (nolock)  , ActivityInstance A with(nolock), 
Activity T with(nolock)where AI.processinstanceid = pi.processinstanceid and 
AI.ActivityInstanceId=A.ActivityInstanceId and A.ActivityId=T.ActivityId 
and exists (select 1 from ActivityInstanceList AIL (nolock) 
where AIL.processinstanceid=PI.processinstanceid and AIL.userid = 'Sally' and AIL.ProcessInstanceStatus = 'C') 
) a   
) b
) c where asrserialno between 291 and 300 
问题是:用了分页有时候查询会超时，不用分页sql最多3秒，现在用了结果超时，爆汗，有好的解决方案没？select  * from (
select  Row_number() over (order by ActivityInstanceID desc) as asrserialno,
* from(这里面的是原始不分页sql）c where asrserialno between m and n,这个是分页后套在外层的!

[东那个升]

[永生天地]

瀑布晕

[tyy283]

路过接分

[lonenan]

晕菜晕菜

[klyh1207]

[骑猪看海]

我这也没分页，谁帮我分下

select distinct d.finquiryid, d.frequestid, d.finquirytitle, d.frequesttitle, d.frequestnum, d.fg1g2, d.fregion, isnull(h.fremark, '') as fremark,\r\n            d.finquirycreator, d.finquiryowner, convert(char(10),convert(datetime,d.fstartdate),111) as fstartdate\r\n            , (case when isnull(f.fstatus,-1)=1 then 'Closed' \r\n            when isnull(f.fstatus,-1)=-1 and isnull(g.fstepid,-1)=50 then 'In process(Sales & SST)'\r\n            when isnull(f.fstatus,-1)=-1 and isnull(g.fstepid,-1)=52 then 'In process(Design,Tech & Costing)'\r\n            when isnull(f.fstatus,-1)=-1 and isnull(g.fstepid,-1)=53 then 'In process(Costing)'\r\n            when isnull(f.fstatus,-1)=-1 and isnull(g.fstepid,-1)=54 then 'In process(Market Sample)'\r\n            when isnull(f.fstatus,-1)=-1 and isnull(g.fstepid,-1)=55 then 'In process(Testing Sample)'\r\n            when isnull(f.fstatus,-1)=-1 and isnull(g.fstepid,-1)=56 then 'In process(Testing)'\r\n            when isnull(f.fstatus,-1)=-1 and isnull(g.fstepid,-1)
=57 then 'In process(Sales & SST Comfirm)'\r\n            when d.finprocess=0 and d.fonhold=0 and d.fcancel=0 then 'Closed' when d.finprocess>0 then 'In process'\r\n            when d.fonhold>0 then 'On hold' when d.fcancel>0 then 'Cancel' end) as fstatus, \r\n            (case when d.fg1g2='G1' and isnull(e.fstatus,-1)=-1 and d.fonhold=0 and d.fcancel=0 then 'Undetermined' \r\n            when d.fg1g2='G2' or d.fonhold>0 or d.fcancel>0 then 'None' \r\n            when e.fstatus=0 and isnull(e.fstepid,-1)=59 then 'In process(G1/2 FO & Q1 Technical, QA, Merchandizing)'\r\n            when e.fstatus=0 and isnull(e.fstepid,-1)=61 then 'In process(Q1 Materials Control & Purchasing)' \r\n            when e.fstatus=0 and isnull(e.fstepid,-1)=62 then 'In process(Q1 Technical)' \r\n            when e.fstatus=0 and isnull(e.fstepid,-1)=63 then 'In process(Q1 Production & Technical)' \r\n            when e.fstatus=0 and isnull(e.fstepid,-1)=64 then 'In process(FO Production & Technical, Customer/Sales FO)' \r\n         
   when e.fstatus=0 and isnull(e.fstepid,-1)=65 then 'In process(FO & Q1 QA/QC FO)'\r\n            when e.fstatus=0 and isnull(e.fstepid,-1)=67 then 'In process(Q1 Production Control, Materials Control & Purchasing)'\r\n            when e.fstatus=0 and isnull(e.fstepid,-1)=68 then 'In process(Q1 Production)'\r\n            when e.fstatus=1 then 'Closed' when e.fstatus=2 then 'On hold' when e.fstatus=3 then 'Cancel' end) as fstatus1, e.fnewrangedate \r\n            from (select a.finquiryid, a.frequestid, a.finquirytitle, a.frequesttitle, a.frequestnum, a.fg1g2, a.fregion,  \r\n            a.finquirycreator, a.finquiryowner, convert(char(10),convert(datetime,a.fstartdate),111) as fstartdate \r\n            , c.finprocess, c.fclosed, c.fonhold, c.fcancel \r\n            from (select distinct finquiryid, frequestid, finquirytitle, frequesttitle, frequestnum, fg1g2, fregion, \r\n            finquirycreator, finquiryowner, convert(char(10),convert(datetime,fstartdate),111) as fstartdate\r\n            from tblnrdph
ase1 where fisskip=0) a \r\n            join (select b.finquiryid, b.frequestid, b.frequestnum, \r\n            sum(b.finprocess) as finprocess, sum(b.fclosed) as fclosed, sum(b.fonhold) as fonhold, sum(b.fcancel) as fcancel \r\n            from (select finquiryid, frequestid, frequestnum, \r\n            (case when fstatus=0 then 1 else 0 end) as finprocess, \r\n            (case when fstatus=1 then 1 else 0 end) as fclosed, \r\n            (case when fstatus=2 then 1 else 0 end) as fonhold, \r\n            (case when fstatus=3 then 1 else 0 end) as fcancel \r\n            from tblnrdphase1 where fisskip=0 and fstepid<>69) b \r\n            group by b.finquiryid, b.frequestid, b.frequestnum) c \r\n            on a.finquiryid=c.finquiryid and a.frequestid=c.frequestid and a.frequestnum=c.frequestnum) d \r\n            left join (select a.finquiryid, a.frequestid, a.frequestnum, a.fstatus, a.fnewrangedate, b.fstepid\r\n            from (select finquiryid, frequestid, frequestnum, fstatus, fnewrangedate \r\n    
        from tblnrdphase2 where fisskip=0 and fstepid=68) a\r\n            left join (select finquiryid, frequestid, frequestnum, min(fstepid) as fstepid\r\n            from tblnrdphase2 where fstatus=0 and fisskip=0 and fstepid in (59,61,62,63,64,65,67,68)\r\n            group by finquiryid, frequestid, frequestnum) b\r\n            on a.finquiryid=b.finquiryid and a.frequestid=b.frequestid and a.frequestnum=b.frequestnum) e\r\n            on d.finquiryid=e.finquiryid and d.frequestid=e.frequestid and d.frequestnum=e.frequestnum\r\n            left join (select finquiryid, frequestid, frequestnum, fstatus from tblnrdphase1 \r\n            where fstatus=1 and fisskip=0 and fstepid=57) f on d.finquiryid=f.finquiryid and d.frequestid=f.frequestid and d.frequestnum=f.frequestnum\r\n            left join (select finquiryid, frequestid, frequestnum, min(fstepid) as fstepid\r\n            from tblnrdphase1 where fstatus=0 and fisskip=0 and fstepid<>69\r\n            group by finquiryid, frequestid, frequestnum) g on
 d.finquiryid=g.finquiryid and d.frequestid=g.frequestid and d.frequestnum=g.frequestnum\r\n            left join (select finquiryid, frequestid, frequestnum, fremark from tblnrdphase1 \r\n            where (fstatus=2 or fstatus=3) and fisskip=0 and fstepid=50) h on d.finquiryid=h.finquiryid and d.frequestid=h.frequestid and d.frequestnum=h.frequestnum\r\n            where 1=1 order by d.finquiryid, d.frequestid, d.frequestnum

[sxiaohui8709253]

去SQL板块

[whb147]

不是一般的长，
没有兴趣看下去了