sql—为包含7500万条记录的表优化存储过程查询

crcmnpdw 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(433)

我有一张table AFW_Coverage 包含7500万行。还有一张table AFW_BasicPolInfo 包含大约300万行。
为了从表中获取记录，我编写了以下存储过程：

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

但是，我发现上面的存储过程将数据库固定为100%，尽管我添加了以下索引，我看到它们在执行计划中使用：

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

存储过程的执行时间在6分钟到20分钟或50分钟之间变化（取决于服务器流量和使用情况）
我的问题是：考虑到覆盖率表包含7500万条记录，如何在存储过程中优化此查询？我不是dba，也没有优化慢速运行查询的经验。任何关于如何解决这个问题的见解都是有帮助的。提前谢谢。

sql sql-server database-administration

来源：https://stackoverflow.com/questions/62527510/optimizing-stored-procedure-query-for-a-table-that-contains-75-million-records

3条答案

按热度按时间

um6iljoc1#

首先，链接公共表表达式可能导致复杂的执行计划。我们希望计划简单，易于发动机优化。
那么，让我们从移除第一个开始：

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;

如果列表中有许多列 ams360.AFW_Coverage 表查询列的索引可能会提高性能：

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

然后，你会看到很多最近被删减的数据。您可以尝试的是在advanced中过滤数据，然后读取行详细信息。像这样：

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;

在这里，您可以调试和优化过滤器查询本身，只为需要的列创建索引。
然后，有了需要返回的行，提取它们的详细信息—因为我们使用分页，我相信它会执行得很好，并且花费更少的io。

赞(0）回复(0）举报 2021-07-24

cuxqih212#

根据执行计划，您的查询只查看 Coverage 表，因为您只对具有最新 EffDate . 如果可能，您可以创建一个单独的表，以便仅捕获基于 EffDate 并在查询中使用此表，而不是 Coverage . 每当在中插入/更新行时，您可能希望插入/更新此新表 Coverage table。

赞(0）回复(0）举报 2021-07-24

7gcisfzg3#

没有看到执行计划，就很难看出问题所在。以下是我的建议：
我看到您在表afw\u basicpolinfo上没有任何索引。你也需要索引。如果可能的话，在polid上创建聚集索引，因为它看起来像一个唯一的、窄的、递增的、非空的列。
我看到你没有AFU覆盖率上的聚集索引。我建议您在polid、effdate组合上创建聚集索引。我想这可能是独一无二的组合。此外，polid被用于连接，它可以使连接更快。这也会使cte更快。
我很怀疑，你是否需要一组人。如果你确实需要分组，那么试着让CTE处于你需要的分组级别，然后加入他们。分组操作可能会非常昂贵。

赞(0）回复(0）举报 2021-07-24

我来回答

sql—为包含7500万条记录的表优化存储过程查询

3条答案

相关问题

热门标签

最新问答