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

Question

我有一个 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)

后

Answer 1

这可能是一种更快的更新方式——如果没有看到执行计划很难说，但它可能会为每一行运行 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，那么无法知道完成需要多长时间。