sql - 存储过程很快，直到它被调用 27 次然后变得缓慢

Question

此存储过程在 10 - 20 毫秒（通常）之间完成，直到它在负载测试会话中被调用 27 次，然后每次调用减慢到 300 - 400 毫秒。有明显的跳跃。

重新开始（并在一段时间之间运行几次）之后，它又很快了。

是什么导致它如此急剧地上涨？我怎样才能绕过它？

我的要求是处理 50 个活动/并发用户，但如果大电话需要这么长时间，我会很快完成。

存储过程处理父子关系。首先它清除所有内容，然后尝试从非规范化表参数重新填充它（比代码中的嵌套 DB 调用更快）。

我想删除 FOR 循环，但由于我不知道父级的 ID，我不知道如何在不创建父级后立即通过程序创建子级的情况下为子级保留 FK。

CREATE OR ALTER PROCEDURE dbo.sp_DeleteThenCreateRoleAndMembers
    @PenguineHouseRoleMemberUpdate PenguineHouseRoleMemberUpdateType READONLY
    AS
    BEGIN
    -- clear out all the existing roles and stuff
    DELETE PenguinHouseRoleMember
        FROM PenguinHouseRoleMember phrm 
        INNER JOIN PenguinHouseRole ph on phrm.PenguinHouseRoleId = ph.Id 
        WHERE ph.PenguinHouseId = (
            SELECT TOP 1 PenguinHouseId from @PenguineHouseRoleMemberUpdate
        )

    -- cursor setup
    DECLARE @PenguinHouseId int;
    DECLARE @RoleType int;
    DECLARE @SubRoleIds varchar;
    DECLARE @RoleId int

    DECLARE @RowCnt int = 0;
    DECLARE @CounterId int = 1;
    SELECT @RowCnt = COUNT(*) FROM @PenguineHouseRoleMemberUpdate;
    WHILE @CounterId <= @RowCnt
    BEGIN
        SELECT @PenguinHouseId = PenguinHouseId,
            @RoleType = RoleType,
            @SubRoleIds = SubRoleIds
            FROM @PenguineHouseRoleMemberUpdate

        -- insert parent record
        INSERT INTO PenguinHouseRole (RoleType, PenguinHouseId, CreatedAt, CreatedBy, UpdatedBy, UpdatedAt)
            VALUES(@RoleType, @PenguinHouseId, GETDATE(), 'system', 'system', GETDATE())
        SET @RoleId = SCOPE_IDENTITY()
            
        -- identify children (subroles)
        DROP TABLE IF EXISTS  #TempSubRoleUpdateTable
        SELECT sq.subRoleId INTO #TempSubRoleUpdateTable
            FROM (
                select convert(int, value) subRoleId FROM string_split(@SubRoleIds, ',')
            ) sq

        -- insert children
        IF (@SubRoleIds IS NOT NULL AND LEN(LTRIM(RTRIM(@SubRoleIds))) > 0)
        BEGIN
            INSERT INTO dbo.PenguinHouseRoleMember
            (
                workflowinstanceroleid,
                subroleid,
                AllTasksCompleted,
                Approved,
                SuggestedBySystem,
                SelectedByCurator,
                CreatedAt, CreatedBy, UpdatedBy, UpdatedAt
            )
            select
                @RoleId,
                subRoleId,
                0,
                0,
                1,
                0,
                GETDATE(), 'system', 'system', GETDATE()
            from
                #TempSubRoleUpdateTable
        END

        SET @CounterId = @CounterId + 1
    END -- loop
END

标签： sqlsql-serverdatabasestored-procedurestemp-tables