sql - 加快临时表更新的性能

我有一个 SQL Server 2012 存储过程。我正在填写下面的临时表，这相当简单。但是，在那之后，我正在对其进行一些 UPDATE。

这是我的 T-SQL，用于声明临时表 #SourceTable ，填充它，然后对其进行一些更新。完成所有这些之后，我只需将这个临时表插入到一个新表中，我们正在填充一个 MERGE 语句，该语句连接 DOI 。 DOI 是此处的主要列，您将在下面看到我的 UPDATE 语句在基于此列的多个列上获取 MAX/MIN ，因为该表可以具有具有相同 DOI 的多行。

我的问题是...如何加快填写 #SourceTable 或对其进行更新？我可以创建任何索引吗？我擅长 SQL，但在性能问题上不是最好的。我正在处理临时表中的 60,000,000 条记录。它已经运行了将近4个小时。这是我正在运行的脚本的一次性交易。

CREATE TABLE #SourceTable
(
    DOI VARCHAR(72),
    FullName NVARCHAR(128), LastName NVARCHAR(64),
    FirstName NVARCHAR(64), FirstInitial NVARCHAR(10),
    JournalId INT, JournalVolume VARCHAR(16),
    JournalIssue VARCHAR(16), JournalFirstPage VARCHAR(16),
    JournalLastPage VARCHAR(16), ArticleTitle NVARCHAR(1024),
    PubYear SMALLINT, CreatedDate SMALLDATETIME,
    UpdatedDate SMALLDATETIME,
    ISSN_e VARCHAR(16), ISSN_p VARCHAR(16),
    Citations INT, LastCitationRefresh SMALLDATETIME,
    LastCitationRefreshValue SMALLINT, IsInSearch BIT,
    BatchUpdatedDate SMALLDATETIME, LastIndexUpdate SMALLDATETIME,
    ArticleClassificationId INT, ArticleClassificationUpdatedBy INT,
    ArticleClassificationUpdatedDate SMALLDATETIME,
    Affiliations VARCHAR(8000),
    --Calculated columns for use in importing...
    RowNum SMALLINT, MinCreatedDatePerDOI SMALLDATETIME,
    MaxUpdatedDatePerDOI SMALLDATETIME,
    MaxBatchUpdatedDatePerDOI SMALLDATETIME,
    MaxArticleClassificationUpdatedByPerDOI INT,
    MaxArticleClassificationUpdatedDatePerDOI SMALLDATETIME,
    AffiliationsSameForAllDOI BIT, NewArticleId INT
)

--***************************************
--CROSSREF_ARTICLES
--***************************************
--GET RAW DATA INTO SOURCE TABLE TEMP TABLE..
INSERT INTO #SourceTable
    SELECT
        DOI, FullName, LastName, FirstName, FirstInitial,
        JournalId, LEFT(JournalVolume,16) AS JournalVolume,
        LEFT(JournalIssue,16) AS JournalIssue,
        LEFT(JournalFirstPage,16) AS JournalFirstPage,
        LEFT(JournalLastPage,16) AS JournalLastPage,
        ArticleTitle, PubYear, CreatedDate, UpdatedDate,
        ISSN_e, ISSN_p,
        ISNULL(Citations,0) AS Citations, LastCitationRefresh,
        LastCitationRefreshValue, IsInSearch, BatchUpdatedDate,
        LastIndexUpdate, ArticleClassificationId,
        ArticleClassificationUpdatedBy,
        ArticleClassificationUpdatedDate, Affiliations,
        ROW_NUMBER() OVER(PARTITION BY DOI ORDER BY UpdatedDate DESC, CreatedDate ASC) AS RowNum,
        NULL AS MinCreatedDatePerDOI, NULL AS MaxUpdatedDatePerDOI,
        NULL AS MaxBatchUpdatedDatePerDOI,
        NULL AS MaxArticleClassificationUpdatedByPerDOI,
        NULL AS ArticleClassificationUpdatedDatePerDOI,
        0 AS AffiliationsSameForAllDOI, NULL AS NewArticleId
    FROM
        CrossRef_Articles WITH (NOLOCK)

--UPDATE SOURCETABLE WITH MAX/MIN/CALCULATED VALUES PER DOI...
UPDATE S
SET MaxUpdatedDatePerDOI = T.MaxUpdatedDatePerDOI, MaxBatchUpdatedDatePerDOI = T.MaxBatchUpdatedDatePerDOI, MinCreatedDatePerDOI = T.MinCreatedDatePerDOI, MaxArticleClassificationUpdatedByPerDOI = T.MaxArticleClassificationUpdatedByPerDOI, MaxArticleClassificationUpdatedDatePerDOI = T.MaxArticleClassificationUpdatedDatePerDOI
FROM #SourceTable S
INNER JOIN (SELECT MAX(UpdatedDate) AS MaxUpdatedDatePerDOI, MIN(CreatedDate) AS MinCreatedDatePerDOI, MAX(BatchUpdatedDate) AS MaxBatchUpdatedDatePerDOI, MAX(ArticleClassificationUpdatedBy) AS MaxArticleClassificationUpdatedByPerDOI, MAX(ArticleClassificationUpdatedDate) AS MaxArticleClassificationUpdatedDatePerDOI, DOI from #SourceTable GROUP BY DOI) AS T ON S.DOI = T.DOI
    UPDATE S
        SET AffiliationsSameForAllDOI = 1
        FROM #SourceTable S
        WHERE NOT EXISTS (SELECT 1 FROM #SourceTable S2 WHERE S2.DOI = S.DOI AND S2.Affiliations <> S.Affiliations)

后

最佳答案

这可能是一种更快的更新方式——如果没有看到执行计划很难说，但它可能会为每一行运行 GROUP BY。

with doigrouped AS
(
  SELECT
    MAX(UpdatedDate) AS MaxUpdatedDatePerDOI,
    MIN(CreatedDate) AS MinCreatedDatePerDOI,
    MAX(BatchUpdatedDate) AS MaxBatchUpdatedDatePerDOI,
    MAX(ArticleClassificationUpdatedBy) AS MaxArticleClassificationUpdatedByPerDOI,
    MAX(ArticleClassificationUpdatedDate) AS MaxArticleClassificationUpdatedDatePerDOI,
    DOI
  FROM #SourceTable
  GROUP BY DOI
)
UPDATE S
SET MaxUpdatedDatePerDOI = T.MaxUpdatedDatePerDOI,
    MaxBatchUpdatedDatePerDOI = T.MaxBatchUpdatedDatePerDOI,
    MinCreatedDatePerDOI = T.MinCreatedDatePerDOI,
    MaxArticleClassificationUpdatedByPerDOI = T.MaxArticleClassificationUpdatedByPerDOI,
    MaxArticleClassificationUpdatedDatePerDOI = T.MaxArticleClassificationUpdatedDatePerDOI
FROM #SourceTable S
INNER JOIN doigrouped T ON S.DOI = T.DOI

如果它更快，它会快几个数量级——但这并不意味着你的机器将能够在任何时间段内处理 6000 万条记录......如果你没有先测试 100k，那么无法知道完成需要多长时间。

关于sql - 加快临时表更新的性能，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34365698/