select count(*) FROM [View_AgentPurchaseOrder] with(nolock)
where 1=1 and (
exists (select null from userrole where rDepartment in (2) and rUser=aoUid)
or exists (select null from userrole where rDepartment in (2) and rStaff=aoSalesMan)
or exists (select null from u_t346_data where exists (select rcode from t_flowrecord where rstate=-1 and ((rallowdid like '%,2,%') or rallowdid='' or rallowdid is null) and ((rallowpid like '%,24,%') or rallowpid='' or
rallowpid is null) and (rallowuser like '%,173,%' or rallowuser='' or rallowuser is null) and rtable=346 and tcode=rcode) and tCode='code_'+cast(View_AgentPurchaseOrder.id as nvarchar(50)))
or exists (select null from u_t346_data where exists (select rcode from t_flowrecord where rUid=173 and rtable=346 and tcode=rcode) and tCode='code_'+cast(View_AgentPurchaseOrder.id as nvarchar(50)))
)
and (aostate>-50 or aoState<-50)
and aocode like 'ap%' and aoTotalAmount>=0
这条语句查询速度很慢查询完成需要38秒,各位有没有什么优化意见
如果去掉
or exists (select null from u_t346_data where exists (select rcode from t_flowrecord where rstate=-1 and ((rallowdid like '%,2,%') or rallowdid='' or rallowdid is null) and ((rallowpid like '%,24,%') or rallowpid='' or
rallowpid is null) and (rallowuser like '%,173,%' or rallowuser='' or rallowuser is null) and rtable=346 and tcode=rcode) and tCode='code_'+cast(View_AgentPurchaseOrder.id as nvarchar(50)))
or exists (select null from u_t346_data where exists (select rcode from t_flowrecord where rUid=173 and rtable=346 and tcode=
rcode) and tCode='code_'+cast(View_AgentPurchaseOrder.id as nvarchar(50))) 这两段的话就查询就只需要1秒
View_AgentPurchaseOrder 这个表数量及在200万左右