1
votes

Optimisation de la requête de procédure stockée pour une table contenant 75 millions d'enregistrements

J'ai une table AFW_Coverage qui contient 75 millions de lignes. Il existe également une autre table AFW_BasicPolInfo qui contient environ 3 millions de lignes.

J'ai écrit la procédure stockée suivante pour obtenir des enregistrements de la table:

CREATE NONCLUSTERED INDEX [IX_AFW_Coverage_PolId_EffDate] 
ON [ams360].[AFW_Coverage] ([PolId] ASC, [EffDate] ASC)
            WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_AFW_Coverage_PolId_EffDate_Status_LobId_CoverageId] 
ON [ams360].[AFW_Coverage] ([PolId] ASC, [EffDate] ASC, [Status] ASC, [LobId] ASC, [CoverageId] ASC)
INCLUDE ([CoverageCode], [IsCoverage], [FullTermPrem], [Limit1], [Limit2],[Limit3], [Deduct1], [Deduct2], [Deduct3], [ChangedDate], [RowVersion]) 
        WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

Cependant, je trouve que la procédure stockée ci-dessus ancre la base de données à 100% bien que j'aie ajouté les index suivants que je vois qu'ils sont utilisés dans le plan d'exécution:

CREATE PROCEDURE [ams360].[GetPolicyCoverages]
    @PageStart INT = 0,
    @PageSize INT = 50000,
    @RowVersion TIMESTAMP = NULL
AS
    SET NOCOUNT ON;

    ;WITH LatestCoverage AS
    (
        SELECT 
            PolId,
            MAX(EffDate) AS CoverageEffectiveDate 
        FROM 
            ams360.AFW_Coverage 
        GROUP BY 
            PolId
    ),
    Coverages AS
    (
        SELECT 
            cov.PolId,
            cov.LobId,
            cov.CoverageId,
            cov.EffDate, 
            cov.CoverageCode,
            cov.isCoverage,
            cov.FullTermPrem,
            cov.Limit1,
            cov.Limit2,
            cov.Limit3,
            cov.Deduct1,
            cov.Deduct2,
            cov.Deduct3,
            cov.ChangedDate,
            cov.RowVersion,
        FROM
            ams360.AFW_Coverage cov
        INNER JOIN
            LatestCoverage mcov ON cov.PolId = mcov.PolId
                                AND cov.EffDate = mcov.CoverageEffectiveDate
        WHERE
            cov.Status IN ('A', 'C')
    )
    SELECT
        BPI.PolId,
        BPI.PolEffDate,
        BPI.PolExpDate,
        BPI.PolTypeLOB,
        cov.LobId,
        cov.CoverageId,
        cov.EffDate,
        cov.CoverageCode,
        cov.isCoverage,
        cov.FullTermPrem,
        cov.Limit1,
        cov.Limit2,
        cov.Limit3,
        cov.Deduct1,
        cov.Deduct2,
        cov.Deduct3,
        cov.ChangedDate,
        cov.RowVersion,
    FROM 
        ams360.AFW_BasicPolInfo BPI 
    INNER JOIN 
        Coverages cov ON bpi.PolId = cov.PolId
    WHERE 
        BPI.Status IN ('A','C')
        AND BPI.PolTypeLOB IN ('Homeowners', 'Dwelling Fire')
        AND BPI.PolSubType = 'P'
        AND BPI.RenewalRptFlag IN ('A', 'R', 'I', 'N')
        AND GETDATE() BETWEEN BPI.PolEffDate AND BPI.PolExpDate
        AND (@RowVersion IS NULL OR cov.RowVersion > @RowVersion)
    GROUP BY 
        BPI.PolId,
        BPI.PolEffDate,
        BPI.PolExpDate,
        BPI.PolTypeLOB,
        cov.LobId,
        cov.CoverageId,
        cov.EffDate,
        cov.CoverageCode,
        cov.isCoverage,
        cov.FullTermPrem,
        cov.Limit1, cov.Limit2, cov.Limit3,
        cov.Deduct1, cov.Deduct2, cov.Deduct3,
        cov.ChangedDate,
        cov.RowVersion,
    ORDER BY 
        cov.RowVersion
    OFFSET 
        @PageStart ROWS
    FETCH NEXT 
        @PageSize ROWS ONLY
GO

Le temps d'exécution de la procédure stockée varie entre 6 minutes et 20 minutes ou 50 minutes (selon le trafic et l'utilisation du serveur)

Ma question: Comment optimiser cette requête dans la procédure stockée en gardant à l'esprit que le tableau de couverture contient 75 millions d'enregistrements? Je ne suis pas un dba et je n'ai aucune expérience préalable de l'optimisation des requêtes lentes. Toute idée de la façon de résoudre ce problème serait utile. Merci d'avance.

sql sql-server database-administration

6 commentaires

Pouvez-vous également publier le plan d'exécution pour cette requête? Il faut regarder le plan pour comprendre quelles étapes ont des coûts proportionnellement plus élevés et essayer de les optimiser.

Comment publier mon plan d'exécution ici?

Veuillez consulter cette méta sur la publication de plans.

Merci. Voici le lien vers le plan d'exécution: brentozar.com/pastetheplan/?id=H1dXyfyAL

Pouvez-vous également publier le plan d'exécution réel si possible? Voici comment vous pouvez l'obtenir

Voici le plan d'exécution actuel: brentozar.com/pastetheplan/?id=BkR6sDyAU

3 Réponses :

0
votes

Sans voir le plan d'exécution, il est très difficile de dire le problème. Voici mes suggestions:

Je vois que vous n'avez aucun index sur la table AFW_BasicPolInfo. Vous devez également avoir des index sur eux. Si possible, créez un index clusterisé sur PolId, car il semble être une colonne unique, étroite, croissante et non nulle.
Je vois que vous n'avez pas d'index clusterisé sur AFW_Coverage. Je vous suggère de créer un index clusterisé sur la combinaison PolId, EffDate. Je pense que cela pourrait être une combinaison unique. De plus, PolId étant utilisé dans les JOINs, cela pourrait rendre les JOINS plus rapides. Cela rendrait également le CTE plus rapide.
Je doute sérieusement que vous ayez besoin de GROUP By. Si vous avez besoin de GROUP BY à coup sûr, essayez d'avoir des CTE au niveau de regroupement dont vous avez besoin, puis REJOIGNEZ-les. GROUP BY pourrait être une opération très coûteuse.

0 commentaires

2
votes

Premièrement, le chaînage d'une expression de table commune peut conduire à un plan d'exécution complexe. Nous voulons que les plans soient simples et faciles à optimiser pour le moteur.

Commençons donc par supprimer le premier:

DROP TABLE if exists #CoveragesFiltered 

CREATE TABLE #CoveragesFiltered
(
     PolId BIGINT PRIMARY KEY
    ,RowVersion ??
);

INSERT INTO #CoveragesFiltered
SELECT 
    cov.PolId,       
    cov.RowVersion,
FROM ams360.AFW_Coverage cov
INNER JOIN #LatestCoverage mcov 
    ON cov.PolId = mcov.PolId
    AND cov.EffDate = mcov.CoverageEffectiveDate
WHERE
    cov.Status IN ('A', 'C')
    AND BPI.Status IN ('A','C')
    AND BPI.PolTypeLOB IN ('Homeowners', 'Dwelling Fire')
    AND BPI.PolSubType = 'P'
    AND BPI.RenewalRptFlag IN ('A', 'R', 'I', 'N')
    AND GETDATE() BETWEEN BPI.PolEffDate AND BPI.PolExpDate
    AND (@RowVersion IS NULL OR cov.RowVersion > @RowVersion)
ORDER BY 
    cov.RowVersion
OFFSET 
    @PageStart ROWS
FETCH NEXT 
    @PageSize ROWS ONLY;

S'il y a beaucoup de colonnes dans le ams360.AFW_Coverage table un index sur les colonnes interrogées peut améliorer les performances:

CREATE INDEX IX_AFW_Coverage_EffDate  ON ams360.AFW_Coverage 
(
    polID
    ,EffDate            
)

Ensuite, vous lisez beaucoup de données qui ont été récemment coupées . Ce que vous pouvez essayer, c'est de filtrer les données de manière avancée, puis de lire les détails de la ligne. Quelque chose comme ceci:

DROP TABLE IF EXISTS #LatestCoverage;

CREATE TABLE #LatestCoverage
(
    PolId BIGINT PRIMARY KEY
   ,CoverageEffectiveDate DATETIME2(0)
);

INSERT INTO #LatestCoverage
SELECT 
    PolId,
    MAX(EffDate) AS CoverageEffectiveDate 
FROM 
    ams360.AFW_Coverage 
GROUP BY 
    PolId;

Ici, vous pouvez déboguer et optimiser la requête de filtre elle-même, en créant des index uniquement pour les colonnes dont vous avez besoin.

Ensuite, avoir le lignes qui doivent être renvoyées, extrayez leurs détails - comme nous utilisons la pagination, je pense que cela fonctionnera bien et coûtera moins d'E / S.

2 commentaires

Je vois que vous créez d'abord une table temporaire, puis que vous l'insérez dans cette table temporaire. Mais puis-je faire un Select .... INTO tentable FROM ....?

@CrazyCoder Vous pouvez, mais je préfère la première approche car il est plus facile et plus efficace de créer une clé primaire (et d'autres index) afin d'optimiser les jointures ultérieures.

1
votes

Sur la base des plans d'exécution, votre requête ne regarde que moins de 1% des lignes de la table Coverage car vous n'êtes intéressé que par les lignes ayant le dernier EffDate . Si possible, vous pouvez créer une table séparée pour capturer uniquement les dernières lignes basées sur EffDate et utiliser cette table dans votre requête au lieu de Coverage . Vous pouvez insérer / mettre à jour cette nouvelle table chaque fois que des lignes sont insérées / mises à jour dans la table Coverage .

0 commentaires