Как ускорить SQL-запрос с помощью JOIN на большом поле varchar и NOT EXISTS

Question

У меня есть этот запрос, который будет длиться вечно. Таблица содержит около 7 миллионов строк. Все остальное, что я делаю с этим (это "временный" постоянный стол), происходит относительно быстро (около часа), в то время как это одно обновление заняло 7 часов! У нас есть SQL Server 2014.

DOI является NVARCHAR(72) и имеет неуникальный CLUSTERED Индекс на это. Affiliations это VARCHAR(8000), Мне не разрешено менять эти типы данных. Affiliations имеет индекс для включения. Мы не могли сделать "обычный" индекс, так как поле очень большое.

CREATE NONCLUSTERED INDEX IX_Affiliations 
    ON TempSourceTable (DOI) INCLUDE (Affiliations);

То, что делает оператор ниже, устанавливает битовое поле равным 1, если все записи для DOI имеют одинаковое значение в их Affiliations колонка. Эта таблица имеет несколько записей на DOI значение, и мы хотим знать, если Affiliations столбец одинаков для всех записей с тем же DOI или нет.

Есть ли способ, которым я могу ускорить это, написав другой запрос, другой индекс, или я все об этом не так?

UPDATE S
SET AffiliationsSameForAllDOI = 1
FROM TempSourceTable S
WHERE NOT EXISTS (SELECT 1 
                  FROM TempSourceTable S2 
                  WHERE S2.DOI = S.DOI 
                    AND S2.Affiliations <> S.Affiliations)

Answer 1

Вот еще один способ

SUB-QUERY версия

UPDATE TempSourceTable
SET    AffiliationsSameForAllDOI = 1
WHERE  doi IN (SELECT doi
               FROM   TempSourceTable S
               GROUP  BY DOI
               HAVING COUNT(DISTINCT Affiliations) = 1) 

EXISTS Версия

UPDATE TempSourceTable S
SET    AffiliationsSameForAllDOI = 1
WHERE EXISTS  (SELECT 1
               FROM   TempSourceTable S1
               Where s1.DOI = s.DOI
               HAVING COUNT(DISTINCT Affiliations) = 1) 

INNER JOIN Версия

UPDATE S 
SET    AffiliationsSameForAllDOI = 1
FROM TempSourceTable S
INNER JOIN (SELECT doi
            FROM   TempSourceTable 
            GROUP  BY DOI
            HAVING COUNT(DISTINCT Affiliations) = 1) S1
        ON S.DOI = S1.DOI

• FIDDLE DEMO

Answer 2

update TempSourceTable
set AffiliationsSameForAllDOI = 1
where DOI in (
    select DOI
    from TempSourceTable
    group by DOI
    having count(distinct Affiliations) = 1
)

В зависимости от того, как выглядят ваши данные, возможно, вам повезет с производительностью, если вы создадите вычисляемый столбец, который вычеркивает, скажем, первые 16 символов из Affiliations или просто используя checksum() а затем вместо этого индексировать по этому столбцу. Возможно, это будет выглядеть примерно так:

update TempSourceTable
set AffiliationsSameForAllDOI = 1
where DOI in (
    select DOI
    from TempSourceTable
    where DOI in (
        select DOI
        from TempSourceTable
        group by DOI
        having count(distinct AffiliationsChecksum) = 1
    )
    group by DOI
    having count(distinct Affiliations) = 1
)

Answer 3

Я надеюсь, что это будет работать лучше, чем другие предложения, так как он должен выполняться за один просмотр индекса. Кроме того, мин / макс "трюк" позволяет избежать сбора и поддержания каждого отдельного значения.

WITH X AS 
(
  SELECT *,
    AffiliationsSameForAllDOI_New =
      CASE WHEN MAX(Affiliations) OVER (PARTITION BY DOI)
        = MIN(Affiliations) OVER (PARTITION BY DOI)
      THEN 1
      ELSE 0
      END
  FROM TempSourceTable 
)
UPDATE X
SET AffiliationsSameForAllDOI = AffiliationsSameForAllDOI_New
WHERE AffiliationsSameForAllDOI_New = 1