我正在使用一个存储过程，该过程为表中可用的所有节点生成SystemTree排名。
我的存储过程工作正常，但问题是执行时间太长。
以下是表详细信息：
SystemTree- 14000+ 行PaymentSchedule- 5000+ 行MasterRankChart- 仅 15 行
我需要优化我的存储过程。执行至少需要 20 分钟。
这是我的存储过程：
ALTER PROCEDURE[dbo].[RankGeneration]
@CreatedUser nvarchar(128),
@CreatedOn datetime
AS
BEGIN
DECLARE @NodeKeyId nvarchar(128)
DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
SELECT TOP 1000 NodeKeyId
FROM SystemTree
DECLARE @RankContainer TABLE
(
NodeKeyId nvarchar(128),
[Rank] nvarchar(512),
RankId int,
[LargestLeg] nvarchar(128),
[LargestLegNV] decimal(18, 2),
[SecondLargestLeg] nvarchar(128),
[SecondLargestLegNV] decimal(18, 2),
[ThirdPlusLeg] nvarchar(max),
[ThirdPlusLegNV] decimal(18, 2)
)
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @NodeKeyId
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @YearlyMinPNV bigint
DECLARE @CurrentNodeId nvarchar(128)
DECLARE @TempNodeKeyId nvarchar(128)
DECLARE @TempChildKeyId nvarchar(128)
DECLARE @TempParentKeyId nvarchar(128)
DECLARE @TempPlacementNode hierarchyid
DECLARE @IMMEDIATEIDs TABLE
(
NodeKeyId nvarchar(128),
PlacementNode hierarchyid
)
INSERT INTO @IMMEDIATEIDs
SELECT NodeKeyId, PlacementNode
FROM SystemTree
WHERE PlacementNode.GetAncestor(1) = (SELECT PLACEMENTNODE
FROM SystemTree
WHERE NodeKeyId = @NodeKeyId)
DECLARE @ChildIDs TABLE
(
ParentNodeId nvarchar(128),
NodeKeyId nvarchar(128)
)
DECLARE @FinalNV TABLE
(
ParentNodeId nvarchar(128),
NodeKeyId nvarchar(128),
TotalNV decimal(18, 2)
)
DECLARE @ResultNV TABLE
(
ImmediateNodeID nvarchar(128),
TotalNV decimal(18, 2)
)
DECLARE @StorageNV TABLE
(
NodeKeyId nvarchar(128),
NV decimal(18, 2)
)
INSERT INTO @StorageNV
SELECT NodeKeyId, SUM(NV)
FROM PaymentSchedule
WHERE ClearDate IS NOT NULL
AND NV IS NOT NULL
GROUP BY NodeKeyId
DECLARE @i INT
DECLARE @count INT
SET @i = 0
SELECT @count = COUNT(*)
FROM @IMMEDIATEIDs
WHILE @i < @count
BEGIN
SELECT
@TempNodeKeyId = NodeKeyId,
@TempPlacementNode = PlacementNode
FROM
@IMMEDIATEIDs
ORDER BY
NodeKeyId
OFFSET (@i) ROWS FETCH NEXT 1 ROWS ONLY
INSERT @ChildIDs
SELECT
@TempNodeKeyId AS ParentNodeId,
t.NodeKeyId
FROM SystemTree t
WHERE PlacementNode.IsDescendantOf(@TempPlacementNode) = 1
AND t.NodeKeyId IN (SELECT NodeKeyId
FROM @StorageNV)
SET @i = @i + 1
END
SET @i = 0
SELECT @count = COUNT(*)
FROM @ChildIDs
WHILE @i < @count
BEGIN
SELECT
@TempChildKeyId = NodeKeyId,
@TempParentKeyId = ParentNodeId
FROM
@ChildIDs
ORDER BY
NodeKeyId
OFFSET (@i) ROWS FETCH NEXT 1 ROWS ONLY
INSERT INTO @FinalNV
VALUES(@TempParentKeyId, @TempChildKeyId,
(SELECT SUM(NV)
FROM @StorageNV
WHERE NodeKeyId = @TempChildKeyId));
SET @i = @i + 1
END
INSERT INTO @ResultNV
SELECT ParentNodeId, SUM(TotalNV)
FROM @FinalNV
GROUP BY ParentNodeId
DECLARE @MainCheckResult decimal(18, 2);
DECLARE @LargestLeg nvarchar(128);
DECLARE @MiddleCheckResult decimal(18, 2);
DECLARE @SecondLargestLeg nvarchar(128);
DECLARE @ThirdCheckResult decimal(18, 2);
DECLARE @ThirdPlusLeg nvarchar(128) = NULL;
SET @MainCheckResult = ISNULL((SELECT TOP(1) TotalNV
FROM @ResultNV
ORDER BY TotalNV DESC), 0);
SET @LargestLeg = (SELECT TOP(1) ImmediateNodeID
FROM @ResultNV
ORDER BY TotalNV DESC)
SET @MiddleCheckResult = ISNULL((SELECT TOP(1) TotalNV
FROM @ResultNV
WHERE ImmediateNodeID NOT IN (SELECT TOP(1) ImmediateNodeID
FROM @ResultNV
ORDER BY TotalNV DESC)
ORDER BY TotalNV DESC), 0);
SET @SecondLargestLeg = (SELECT TOP(1) ImmediateNodeID
FROM @ResultNV
WHERE ImmediateNodeID NOT IN (SELECT TOP (1) ImmediateNodeID
FROM @ResultNV
ORDER BY TotalNV DESC)
ORDER BY TotalNV DESC)
SET @ThirdCheckResult = ISNULL((SELECT SUM(TotalNV)
FROM @ResultNV
WHERE ImmediateNodeID NOT IN (SELECT TOP(2) ImmediateNodeID
FROM @ResultNV
ORDER BY TotalNV DESC)), 0);
SET @ThirdPlusLeg = NULL;
INSERT INTO @RankContainer
SELECT TOP(1)
@NodeKeyId AS NodeKeyId,
[Rank],
Id AS RankId,
@LargestLeg AS [LargestLeg],
@MainCheckResult AS [LargestLegNV],
@SecondLargestLeg AS [SecondLargestLeg],
@MiddleCheckResult AS [SecondLargestLegNV],
@ThirdPlusLeg AS [ThirdPlusLeg],
@ThirdCheckResult AS [ThirdPlusLegNV]
FROM
MasterRankChart
WHERE
LargestLegNV + SecondLegNV + ThirdLegNV <= @MainCheckResult + @MiddleCheckResult + @ThirdCheckResult
AND SecondLegNV + ThirdLegNV <= @MiddleCheckResult + @ThirdCheckResult
AND ThirdLegNV <= @ThirdCheckResult
ORDER BY
Priority
FETCH NEXT FROM MY_CURSOR INTO @NodeKeyId
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
MERGE TrackRank AS Target
USING(SELECT
NodeKeyID, [Rank], [RankId],
[LargestLeg], [LargestLegNv],
SecondLargestLeg, SecondLargestLegNV,
ThirdPlusLeg, ThirdPlusLegNV
FROM @RankContainer) AS Source ON (Target.NodeKeyId = Source.NodeKeyId)
WHEN MATCHED THEN
UPDATE
SET Target.[Rank] = Source.[Rank],
Target.[RankId] = Source.[RankId],
Target.[LargestLeg] = Source.[LargestLeg],
Target.[LargestLegNv] = Source.[LargestLegNv],
Target.SecondLargestLeg = Source.SecondLargestLeg,
Target.SecondLargestLegNV = Source.SecondLargestLegNV,
Target.ThirdPlusLeg = Source.ThirdPlusLeg,
Target.ThirdPlusLegNV = Source.ThirdPlusLegNV
WHEN NOT MATCHED BY TARGET THEN
INSERT (NodeKeyId, [Rank], [RankId], LargestLeg, LargestLegNV,
SecondLargestLeg, SecondLargestLegNV,
ThirdPlusLeg, ThirdPlusLegNV,
CreatedOn, UpdatedOn, IsDeleted, CreatedBy)
VALUES (Source.NodeKeyID, Source.[Rank], Source.[RankId],
Source.[LargestLeg], Source.[LargestLegNv],
Source.SecondLargestLeg, Source.SecondLargestLegNV,
Source.ThirdPlusLeg, Source.ThirdPlusLegNV,
@CreatedOn, @CreatedOn, 0, @CreatedUser)
OUTPUT $ACTION, INSERTED.*, DELETED.*;
END
请看一下任何给我如何优化它的建议。
谢谢和问候，
高先生
第一个建议是避免CURSOR- 每次你可以避免CURSOR时，你应该这样做。另一个建议，看看执行计划，也许您将能够检测到瓶颈等。
以下内容将有助于优化查询执行时间：
- 尝试在不使用游标的情况下运行
- 在引用表上创建索引
- 打开 SQL Server 事件探查器，然后运行查询并将跟踪文件保存在探查器工具中。然后在 SQL Server 优化顾问中打开该跟踪文件 (
*.trc) 并估计它，这将指导我们最小化执行时间。