Альтернатива "кроме" в SQL с производительностью

Question

У меня есть таблица TableA, как в MS-Sql

 TrId   Status
 2345   3
  567   3
  567   0
 2345   0
   99   3
  778   0

Сценарий состоит в том, что три идентификатора имеют статус как 3, так и 0, некоторые имеют только 3, а некоторые только 0. Мне нужно найти TrIds только со статусом 3.
Один из способов сделать это:

Select TrnId From TableA Where flgStatus = 3
EXCEPT
Select TrnId From Tablea Where flgStatus = 0

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

Answer 1

Является ли комбинация (TrnId,flgStatus) уникальным?

Тогда вы можете перейти к EXCEPT ALL, похожий на UNION ALL который может быть более эффективным, чем UNION потому что он избегает операции DISTINCT.

Другое решение, которое обращается к базовой таблице только один раз:

Select TrnId 
From TableA Where flgStatus in (0,3)
group by TrnId
having MIN(flgStatus) = 3

Answer 2

Ты можешь использовать NOT EXISTS

SELECT *
FROM TableA a
WHERE flgStatus = 3
AND NOT EXISTS 
(SELECT TrnId From TableA b Where flgStatus = 0 AND a.TrnId = b.TrnId) 

Это обычно имеет лучшую производительность, чем NOT IN, Хорошей альтернативой будет соединение, см. Ответ @ThomasG.

Answer 3

EXCEPT или же MINUS это правильная вещь здесь. На очень большом столе это, однако, не оптимально.

Альтернативой будет это

SELECT *
FROM TableA 
WHERE flgStatus = 3
AND TrnId  NOT IN
(SELECT TrnId From TableA Where flgStatus = 0) 

Или даже лучше, используя LEFT JOIN а также IS NULL чтобы избежать NOT который является убийцей перфораторов:

SELECT *
FROM TableA T3
LEFT JOIN TableA T0 ON T3.TrnId = T0.TrnId AND T0.flgStatus = 0
WHERE T3.flgStatus = 3
  AND T0.TrnId IS NULL

Редактировать: NOT EXISTS Решение от Игоря тоже хороший подход

Answer 4

Я бы использовал простой group by:

select trnid
from tablea
group by trnid
having min(status) = max(status) and min(status) = 3;

Будет ли это быстрее или нет, зависит от нескольких вещей.,, в частности, хотите ли вы удалить дубликаты и какие у вас есть индексы для данных. NOT EXISTS скорее всего будет быстрее, если вы не заботитесь о дубликатах, но устранение дубликатов требует работы.

Answer 5

Для большого набора данных, подобного вашему, использование следующего запроса может дать желаемый результат с разумной производительностью:

SELECT ta1.TrId AS TrId
FROM dbo.TableA AS ta1
LEFT JOIN dbo.TableA AS ta2 ON (ta2.TrId = ta1.TrId AND ta2.[Status] != 3)
WHERE ta2.TrId IS NULL;

Во-первых, самосоединение создает таблицу, упорядочивая все состояния (3 или 0, 1 и т. Д.) В одной строке. Фильтр

 ta2.[Status] != 3

в предложении соединения ставит NULL для ta2.TrId (или ta2.*), если Status равен 3.

+------+--------+------+--------+
| TrId | Status | TrId | Status |
+------+--------+------+--------+
| 2345 |      3 | 2345 | 0      |
|  567 |      3 | 567  | 0      |
|  567 |      0 | 567  | 0      |
| 2345 |      0 | 2345 | 0      |
|   99 |      3 | NULL | NULL   |
|  778 |      0 | 778  | 0      |
+------+--------+------+--------+

Затем следующий фильтр используется для выбора строк, в которых встречается NULL.

WHERE ta2.TrId IS NULL

Так как это само LEFT JOIN, в левой таблице есть все строки, но NULL для правых значений таблицы, где критерии объединения не удовлетворяют.