问题描述

SqlServer 存储过程 多个with as公共表达式 查不出结果 假死

发现SqlServer的一个奇怪问题，有一个存储过程，用了多个With as的公共表达式，运行一段时间后，从应用程序连接这个存储过程就查不出结果，提示查询超时。若把应用程序中的那句Sql直接放到SqlServer中运行能很快查出结果。当应用程序查不出结果时，再把这个存储过程重新运行，应用程序就立马能查出结果。仿佛重新运行存储过程，就释放了一些东西。这是为什么？
存储过程如下：
ALTER PROCEDURE [dbo].[PR_FIN_EmployeeCommission]
@DeptFullCode nvarchar(100)=null,
@StartDate date='2012-10-01',
@EndDate date='2012-10-31',
@ReportType varchar(50)='',
@Trade nvarchar(50)='',
@Estate nvarchar(50)='',
@EmpID nvarchar(50)='',
@RoleName nvarchar(50)='',
@ContractStartDate date='2012-10-01',
@ContractEndDate date='2012-10-31'
AS
BEGIN
SET NOCOUNT ON;
with ContractFiltered as
(
select distinct fin.ContractID

from dbo.FIN_AccountFlowHistory fin with (nolock),dbo.CT_Contract ct with (nolock),dbo.F_Property prop with (nolock),
dbo.F_Estate estate with (nolock)
where fin.MoneyType=1 and fin.FlowType=1 and fin.FlowFlag>=100

and (@StartDate='' or fin.FlowDate>=@StartDate)
and (@EndDate='' or fin.FlowDate<=@EndDate)
and fin.ContractID=ct.ContractID
and ct.ReportFlag>=2
and (@ContractStartDate='' or ct.ContractDate>=@ContractStartDate)
and (@ContractEndDate='' or ct.ContractDate<=@ContractEndDate)
and (@ReportType='' or ct.ReportType=@ReportType)
and (@Trade='' or ct.Trade=@Trade)
and ct.PropertyID=prop.PropertyID and prop.EstateID=estate.EstateID
and (@Estate='' or estate.EstateName like '%'+@Estate+'%')
),
CommissionAssign as
(
select a.ContractID,SUM(a.CommRatePer) as CommRatePer,DeptID
from (
select ct.ContractID, assign.CommRatePer,assign.DeptID
from ContractFiltered ct with (nolock),dbo.CT_ContractCommisionAssigns assign with (nolock)
where ct.ContractID =assign.ContractID and assign.Comment='按揭员'
union all
select ct.ContractID,adjustAssign.CommAdjust,adjustAssign.DeptID
from ContractFiltered ct with (nolock),dbo.CT_CommisionAdjust adjust with (nolock),
dbo.CT_CommisionAdjustAssigns adjustAssign with (nolock)
where ct.ContractID=adjust.ContractID and adjust.AdjustID=adjustAssign.AdjustID
and adjust.AdjustFlag=200 and adjustAssign.Comment='按揭员'
) a
group by a.ContractID,a.DeptID
having SUM(a.CommRatePer)>0
),

FinFiltered as
(
select a.ContractID,SUM(a.FlowMoney) as FlowMoney,a.MortageDeptCode
from (
select fin.FlowMoney,ct.ContractID,
(case when fin.DeptID in (select DeptID from CommissionAssign where ContractID=ct.ContractID)
then 1 else 0 end) as MortageDeptCode
from ContractFiltered ct with (nolock),dbo.FIN_AccountFlowHistory fin with (nolock)
where fin.ContractID=ct.ContractID and fin.MoneyType=1 and fin.FlowType=1 and fin.FlowFlag>=100
and (@StartDate='' or fin.FlowDate>=@StartDate)
and (@EndDate='' or fin.FlowDate<=@EndDate)
) a
group by a.ContractID,MortageDeptCode
),
EmpFin as
(
select IsNUll(SUM(ReceivedComm),0) as ReceivedComm,EmpID
from (
select fin.FlowMoney*empComm.Ratio as ReceivedComm,empComm.EmpID
from FinFiltered fin with (nolock)
inner join dbo.CT_EmployeeCommission empComm with (nolock) on fin.ContractID=empComm.ContractID
where fin.MortageDeptCode=1 and empComm.IsMortgageMember=1
union all
select fin.FlowMoney*empComm.Ratio as ReceivedComm,empComm.EmpID
from FinFiltered fin with (nolock)
inner join dbo.CT_EmployeeCommission empComm with (nolock) on fin.ContractID=empComm.ContractID
where fin.MortageDeptCode=0 and empComm.IsMortgageMember=0
) a
group by EmpID
)

select ROW_NUMBER() over(order by empFin.ReceivedComm desc) as SeqNo,
emp.EmpID,emp.EmpNo,emp.EmpName,dept.DeptName,empFin.ReceivedComm
from EmpFin empFin
inner join dbo.F_Employee emp with (nolock) on empFin.EmpID=emp.EmpID
inner join dbo.F_Department dept with (nolock) on emp.DeptID=dept.DeptID
where (@DeptFullCode is null or @DeptFullCode='' or dept.DeptFullCode like @DeptFullCode+'%')

SET NOCOUNT OFF

END