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

Question

发现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

Answer 1

所有内容均由人工智能模型输出，其内容的准确性和完整性无法保证，不代表我的态度或观点。

该回答引用ChatGPT-3.5，仅为您提供参考，不保证完全正确
你遇到的问题可能涉及到SQL Server执行计划缓存的行为。SQL Server会缓存执行计划以提高查询性能，但有时候缓存的执行计划可能不适用于某些情况，导致查询性能下降或查询超时。

---

以下是可能的解释和解决方法：

1. 执行计划缓存问题：
   当你运行存储过程时，SQL Server会生成并缓存一个执行计划。如果执行计划针对某些参数值优化，但对于其他参数值不适用，那么当你使用不同的参数值调用存储过程时，执行计划可能会导致性能问题。

   解决方法：可以尝试使用WITH RECOMPILE选项，该选项会导致每次调用存储过程时都重新生成执行计划。但请注意，这可能会稍微降低性能，因为执行计划不再被缓存。

   例如：

   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'
   WITH RECOMPILE
   AS
   BEGIN
   ...
   END
   

2. 统计信息问题：
   另一个可能的问题是统计信息过期。如果表的数据分布发生变化，但SQL Server没有正确的统计信息，执行计划可能会不再适用。

   解决方法：可以尝试使用UPDATE STATISTICS命令来更新表的统计信息，以确保SQL Server具有最新的数据分布信息。

   例如：

   UPDATE STATISTICS dbo.FIN_AccountFlowHistory
   UPDATE STATISTICS dbo.CT_Contract
   UPDATE STATISTICS dbo.F_Property
   UPDATE STATISTICS dbo.F_Estate
   

3. 锁问题：
   在存储过程中，你可能会遇到锁问题，特别是在涉及大量数据的查询时。这可能会导致查询超时，而在重新运行存储过程时，锁被释放。

   解决方法：你可以考虑优化查询以减少锁的竞争，或者使用事务隔离级别来管理锁。

4. 查询性能问题：
   最后，存储过程中可能存在查询性能问题，需要进一步优化查询，例如通过索引或查询重写来提高性能。

综合考虑上述问题，你可以首先尝试使用WITH RECOMPILE选项，看看是否解决了问题。如果问题仍然存在，可以进一步检查统计信息和锁问题，以及存储过程中的查询性能，以确定如何优化和修复问题。

---

该回答引用NewBing，仅为您提供参考，不保证完全正确
您好，这是Bing。我尝试了一下您的存储过程，发现可能有以下几个原因导致查询超时的问题：

• 您的存储过程中使用了多个WITH AS的公共表达式，这些表达式可能会导致查询计划的复杂度增加，从而影响查询性能。您可以尝试将这些表达式改写为临时表或者表变量，以减少查询计划的开销。
• 您的存储过程中涉及到了多个表的连接和分组操作，这些操作可能会导致大量的IO和CPU消耗，从而影响查询性能。您可以尝试为这些表的连接字段和分组字段创建合适的索引，以提高查询效率。
• 您的存储过程中使用了参数化查询，这可能会导致查询计划缓存和重用的问题，从而影响查询性能。您可以尝试使用OPTION(RECOMPILE)提示来强制每次执行时重新编译查询计划，以避免参数敏感性或者参数欺骗的问题。

如果您想了解更多关于SQL Server存储过程查询超时的原因和解决方法，您可以参考以下网页：

• SQL Server Downloads | Microsoft：这个网页提供了SQL Server 2022的下载链接，以及一些SQL Server工具和连接器的下载链接。SQL Server 2022是最新版本的SQL Server，它具有更高的性能、安全性和云连接特性，您可以尝试升级您的SQL Server版本，以获得更好的查询体验。
• 解决SQL查询总是 超时已过期 - carekee - 博客园：这个网页介绍了一些常见的SQL查询超时的原因和解决方法，包括数据库设计问题、数据库超时设置、查询语句优化、应用程序连接失败等方面。您可以根据这个网页的内容检查和修改您的数据库配置和查询语句。
• 排查查询超时错误 - SQL Server | Microsoft Learn：这个网页介绍了如何排查SQL Server中出现的查询超时错误，包括如何收集和分析错误信息、如何诊断和优化查询计划、如何监控和调整资源利用率等方面。您可以根据这个网页的内容进行更深入和系统的排查和优化。

---

源: 与必应的对话， 2023/10/6
(1) SQL Server Downloads | Microsoft. https://www.microsoft.com/en-us/sql-server/sql-server-downloads.
(2) 解决SQL查询总是 超时已过期 - carekee - 博客园. https://www.cnblogs.com/carekee/articles/1630492.html.
(3) 排查查询超时错误 - SQL Server | Microsoft Learn. https://learn.microsoft.com/zh-cn/troubleshoot/sql/database-engine/performance/troubleshoot-query-timeouts.

---

该回答引用文心一言，仅为您提供参考，不保证完全正确
输入上限

---

该回答引用讯飞星火，仅为您提供参考，不保证完全正确
这个问题可能是由于SQL Server的查询缓存导致的。当存储过程第一次运行时，查询结果会被缓存起来。当应用程序再次运行存储过程时，它会直接从缓存中获取结果，而不需要再次执行查询。这可能会导致应用程序在运行存储过程之前无法获取到最新的查询结果。

---

为了解决这个问题，可以尝试在存储过程中强制刷新查询缓存。在存储过程的最后添加以下代码：

DBCC FREEPROCCACHE

这将强制SQL Server释放所有缓存的查询计划，确保下一次运行存储过程时会重新执行查询。请注意，这可能会影响性能，因此在生产环境中谨慎使用。

---

该回答引用通义千问，仅为您提供参考，不保证完全正确
根据你的描述，你可能遇到了SQL Server的锁问题。在存储过程中，你使用了多个公共表达式（with as），这些公共表达式可能在查询过程中锁定了某些数据。当应用程序连接到存储过程并尝试查询时，由于锁的存在，可能会导致查询超时。

---

重新运行存储过程可能会释放锁定的数据，从而使应用程序能够查询到结果。这是因为重新运行存储过程可能会重新生成公共表达式，从而释放之前的锁。

---

为了解决这个问题，你可以尝试在查询中添加等待锁的语句，或者尝试优化你的查询以减少锁定的数据。你也可以考虑使用事务来确保数据的一致性，而不是使用存储过程。