我的程序如下:
set [tb1].[SLD_VAR] = case when [CalendarDate] = [tb1].[FileDate] and ([tb2].[SLD] - [tb2].[LAG_SLD])/([tb2].[SLD]+1) >= 0.15 then 1 else 0 end,
[tb1].[StartingDate_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[StartingDate] <> [tb2].[LAG_StartingDate] then 1 else 0 end,
[tb1].[ClosingDate_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[ClosingDate] <> [tb2].[LAG_ClosingDate] then 1 else 0 end,
[tb1].[CurrentStatus_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[CurrentStatus] <> [tb2].[LAG_CurrentStatus] then 1 else 0 end,
[tb1].[Rate_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[Rate] <> [tb2].[LAG_Rate] then 1 else 0 end,
[tb1].[COF_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[COF] <> [tb2].[LAG_COF] then 1 else 0 end,
[tb1].[Term_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[Term] <> [tb2].[LAG_Term] then 1 else 0 end,
[tb1].[Term_GT_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[Term_GT] <> [tb2].[LAG_Term_GT] then 1 else 0 end
from [DataTable] as [tb1]
left join (
select [Filedate]
,cast([Deal] as inT) as [Deal]
,[SLD]
,[CurrentStatus]
,[StartingDate]
,[ClosingDate]
,[Rate]
,[COF]
,[Term],
DATEDIFF(dd,[ApplicationDate],[ClosingDate]) as [Term_GT],
LAG([StartingDate],1) over (partition BY [Deal] order by [FileDate]) as [LAG_StartingDate],
LAG([ClosingDate],1) over (partition BY [Deal] order by [FileDate]) as [LAG_ClosingDate],
LAG([CurrentStatus],1) over (partition BY [Deal] order by [FileDate]) as [LAG_CurrentStatus],
LAG([Rate],1) over (partition BY [Deal] order by [FileDate]) as [LAG_Rate],
LAG([COF],1) over (partition BY [Deal] order by [FileDate]) as [LAG_COF],
LAG([SLD],1) over (partition BY [Deal] order by [FileDate]) as [LAG_SLD],
LAG([Term],1) over (partition BY [Deal] order by [FileDate]) as [LAG_Term],
LAG(DATEDIFF(dd,[ApplicationDate],[ClosingDate]),1) over (partition BY [Deal] order by [FileDate]) as [LAG_Term_GT]
from [DataTable]
where [FileDate] >= DATEADD( day, -14, @Date)
) as [tb2]
on [tb1].[Filedate] = [tb2].[Filedate]
and [tb1].[Deal] = [tb2].[Deal]
where [tb1].[CalendarDate] >= @DateProd
end
将CalendarDate
和Deal
一起作为 PrimaryKey,我创建了一些索引,但仍然需要相当长的时间来执行,并且我需要每周运行此过程。请帮助我制定一些优化策略。
我要做的第一件事就是把它分开一点,这样你就知道哪一部分是最慢的。为此,您需要获取两者的实际执行计划。您可以按照此处的说明进行操作:
获得这些后,如果您仍需要帮助,可以使用“粘贴计划”来共享它们。
您使用的是 SQL Server 2014,这很糟糕,因为较新版本中的性能功能和查询计划诊断确实有助于找出速度缓慢的位置和原因。
首先,将连接的内容转储到#temp 表。我不确定为什么这里需要左连接,但
EXISTS
如果有充分的理由评估不存在的行,请随意删除最后的子查询。第二部分,进行实际更新。