报告运行缓慢，需要步骤我应该检查以使其更快.关于对代码进行故障排除的任何想法

Question

我主要从事中央应用程序的工作。必须跳入修复后端服务器问题。我有一个在SQL Server上运行的报告，花了一点时间，我已经附加了代码。我需要一些帮助来逐步调查这个问题。问题不在于硬件。我已经与系统管理员核实过

ALTER PROCEDURE [dbo].[Patinet_Model]
    @mid_pat UNIQUEIDENTIFIER,
    @m_tblpat dbo.GuidList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @trtnum TABLE 
    ( 
        pid INT NOT NULL,
        Oid UNIQUEIDENTIFIER NOT NULL,
        sid UNIQUEIDENTIFIER NOT NULL,
        UNIQUE(sid, Oid)
    );
    DECLARE @trtper TABLE 
    ( 
        pid INT NOT NULL,
        Oid UNIQUEIDENTIFIER NOT NULL,
        sid UNIQUEIDENTIFIER NOT NULL,
        patgusBIT NOT NULL,
        UNIQUE(sid, Oid)
    );
    WITH Sbb AS
    (
        SELECT
            ROW_NUMBER() OVER (PARTITION BY ug.[smid] ORDER BY aa.vnum) AS rownumber,
            aa.pid, 
            aa.Oid,
            ug.[smid] as sid
        FROM 
            dbo.pat_note aa
        JOIN 
            dbo.[pat_note] ug ON aa.GroupId = ug.[group_id]
                              AND ug.[is_hidden] = 0
        JOIN 
            @m_tblpat cc ON aa.Oid = cc.item
        WHERE 
            aa.communityid = @mid_pat
    )
    INSERT INTO @trtnum (pid, Oid, sid)
        SELECT pid, Oid,  sid
        FROM Sbb
        WHERE rownumber = 1;
    INSERT INTO @trtper (pid, Oid, sid, patgusBIT)
        SELECT 
            aa.pid, aa.Oid, aa.sid, 
            CASE WHEN ee.sid IS NULL THEN 0 ELSE 1 END
        FROM 
            dbo.pat_note aa
        JOIN 
            @m_tblpat dd ON aa.Oid = dd.item
                         AND aa.sid IS NOT NULL
        LEFT JOIN 
            @trtnum ee ON aa.sid = ee.sid AND aa.Oid = ee.Oid
        WHERE 
            aa.communityid = @mid_pat;

    INSERT INTO @trtper (pid, Oid, sid, patgusBIT)
        SELECT pid, Oid, sid, 0 AS patgusBIT
        FROM @trtnum ff
        WHERE NOT EXISTS (SELECT 1 FROM @trtper WHERE sid = ff.sid AND Oid = ff.Oid);
    WITH CTEA AS
    (
        SELECT
            pkOid AS OG,
            s_g
        FROM ct.oj o
        JOIN @m_tblpat t
            ON o.pkOid = t.item
    )
    SELECT 
        s.pid, 
        s.Oid, 
        s.vnum, 
        s.sid, 
        s.GroupID,
        s.communityid,
        s.Dt_Created as createddate,
        s.[Dt_Modified] as modifieddate,
        us.sid as groupsbb_sid, 
        us.overridesGroupSbbscription,
        u.[User_ID] AS u_sid, 
        u.UserNamespace AS u_namespace, 
        u.FName AS u_fname, 
        u.LName AS u_lname, 
        u.StatusText AS u_statusText, 
        u.StatusUpdated AS u_statusUpdated, 
        u.IMAGE AS u_hasImage, 
        isImageShared AS u_isImageShared, 
        u.Status AS u_status,
        GETDATE() AS u_lastvisit, 
        @mid_pat AS u_communityId, 
        u.IsAnonymous AS u_isAnonymous, 
        u.AnonymousName AS u_anonymousName,
        COALESCE(u.OnlineStatus, 1) AS u_onlineStatus,
        u.OnlineStatusText AS u_onlineStatusText,
        u.LastPing AS u_lastPing,
        u.Email AS u_email,
        u.is_activated
    FROM dbo.pat_note s
    JOIN @trtper us
        ON s.pid = us.pid
    JOIN dbo.pat_mu_us u
        ON u.[UID] = us.sid
        AND u.dispatched = 1
        AND u.[st] = 'ADD'
    JOIN Objs t
        ON t.object_guid = s.Oid
    CROSS APPLY dbo.pat_per_check(us.sid, t.rs_g, 1) p
    WHERE s.communityid = @mid_pat
        AND p.has_perms = 1;
END

需要让它更快一点。任何帮助将不胜感激，或者请告诉我应该从DB方面检查的步骤。

Answer 1

尝试使用临时表代替表变量，因为表变量不能是并行操作的一部分，并且引擎没有很好的统计信息。此外，在临时表中也具体化了输入的用户定义类型变量。

ALTER PROCEDURE [dbo].[Patinet_Model]
    @mid_pat UNIQUEIDENTIFIER,
    @m_tblpat dbo.GuidList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #trtnum  
    ( 
        pid INT NOT NULL,
        Oid UNIQUEIDENTIFIER NOT NULL,
        sid UNIQUEIDENTIFIER NOT NULL,
        UNIQUE(sid, Oid)
    );
    CREATE TABLE #trtper 
    ( 
        pid INT NOT NULL,
        Oid UNIQUEIDENTIFIER NOT NULL,
        sid UNIQUEIDENTIFIER NOT NULL,
        patgusBIT NOT NULL,
        UNIQUE(sid, Oid)
    );
    CREATE TABLE #buffer
    (
        item UNIQUEIDENTIFIER
    )
    INSERT INTO #buffer
    SELECT item
    FROM @m_tblpat;
    WITH Sbb AS
    (
        SELECT
            ROW_NUMBER() OVER (PARTITION BY ug.[smid] ORDER BY aa.vnum) AS rownumber,
            aa.pid, 
            aa.Oid,
            ug.[smid] as sid
        FROM 
            dbo.pat_note aa
        JOIN 
            dbo.[pat_note] ug ON aa.GroupId = ug.[group_id]
                              AND ug.[is_hidden] = 0
        JOIN 
            #buffer cc ON aa.Oid = cc.item
        WHERE 
            aa.communityid = @mid_pat
    )
    INSERT INTO #trtnum (pid, Oid, sid)
        SELECT pid, Oid,  sid
        FROM Sbb
        WHERE rownumber = 1;
    INSERT INTO #trtper (pid, Oid, sid, patgusBIT)
        SELECT 
            aa.pid, aa.Oid, aa.sid, 
            CASE WHEN ee.sid IS NULL THEN 0 ELSE 1 END
        FROM 
            dbo.pat_note aa
        JOIN 
            #buffer dd ON aa.Oid = dd.item
                         AND aa.sid IS NOT NULL
        LEFT JOIN 
            #trtnum ee ON aa.sid = ee.sid AND aa.Oid = ee.Oid
        WHERE 
            aa.communityid = @mid_pat;

    INSERT INTO #trtper (pid, Oid, sid, patgusBIT)
        SELECT pid, Oid, sid, 0 AS patgusBIT
        FROM #trtnum ff
        WHERE NOT EXISTS (SELECT 1 FROM #trtper WHERE sid = ff.sid AND Oid = ff.Oid);
    WITH CTEA AS
    (
        SELECT
            pkOid AS OG,
            s_g
        FROM ct.oj o
        JOIN #buffer t
            ON o.pkOid = t.item
    )
    SELECT 
        s.pid, 
        s.Oid, 
        s.vnum, 
        s.sid, 
        s.GroupID,
        s.communityid,
        s.Dt_Created as createddate,
        s.[Dt_Modified] as modifieddate,
        us.sid as groupsbb_sid, 
        us.overridesGroupSbbscription,
        u.[User_ID] AS u_sid, 
        u.UserNamespace AS u_namespace, 
        u.FName AS u_fname, 
        u.LName AS u_lname, 
        u.StatusText AS u_statusText, 
        u.StatusUpdated AS u_statusUpdated, 
        u.IMAGE AS u_hasImage, 
        isImageShared AS u_isImageShared, 
        u.Status AS u_status,
        GETDATE() AS u_lastvisit, 
        @mid_pat AS u_communityId, 
        u.IsAnonymous AS u_isAnonymous, 
        u.AnonymousName AS u_anonymousName,
        COALESCE(u.OnlineStatus, 1) AS u_onlineStatus,
        u.OnlineStatusText AS u_onlineStatusText,
        u.LastPing AS u_lastPing,
        u.Email AS u_email,
        u.is_activated
    FROM dbo.pat_note s
    JOIN #trtper us
        ON s.pid = us.pid
    JOIN dbo.pat_mu_us u
        ON u.[UID] = us.sid
        AND u.dispatched = 1
        AND u.[st] = 'ADD'
    JOIN Objs t
        ON t.object_guid = s.Oid
    CROSS APPLY dbo.pat_per_check(us.sid, t.rs_g, 1) p
    WHERE s.communityid = @mid_pat
        AND p.has_perms = 1;
END

更进一步 - 在临时表中实现公共表表达式。有时 CTE 会导致糟糕的执行计划，因为语句很复杂，并且多次引擎执行 CTE 代码(如果需要，请在此处投票(。

如果这还不够，我们需要查看表上有哪些索引。