Оператор NOT IN замедляет мой запрос

Question

У меня проблема с моим запросом. У меня есть простой пример, который иллюстрирует мой код.

SELECT distinct ID 
FROM Table  
WHERE IteamNumber in (132,434,675) AND Year(DateCreated) = 2019
      AND ID NOT IN (
                     SELECT Distinct ID FROM Table  
                     WHERE IteamNumber in (132,434,675) AND DateCreated < '2019-01-01')

Как видите, я получаю уникальные идентификаторы данных, которые были созданы в 2019 году, а не ранее.

Операторы select работают нормально, но как только я использую оператор NOT IN, запрос может легко занять 1 минуту и ​​более.

Другой мой вопрос, может ли это быть связано с производительностью компьютера / сервера, на котором работает SQL Server для Microsoft Business Central? Потому что тот же запрос отлично работал даже с оператором (NOT IN), но это было в Microsoft Dynamics C5 SQL Server.

Итак, мой вопрос, что-то не так с моим запросом или это в основном проблема с сервером?

ОБНОВЛЕНИЕ: вот реальный пример: это займет 25 секунд, чтобы получить 500 строк

Select count(distinct b.No_),'2014'
from [Line] c    
inner join [Header] a
on a.CollectionNo = c.CollectionNo
Inner join [Customer] b
on b.No_ = a.CustomerNo

where  c.No_ in('2101','2102','2103','2104','2105')
and year(Enrollmentdate)= 2014 
and(a.Resignationdate < '1754-01-01 00:00:00.000' OR a.Resignationdate >= '2014-12-31')


and NOT EXISTS(Select distinct x.No_
                 from [Line] c    
                 inner join [Header] a
                 on a.CollectionNo = c.CollectionNo
                 Inner join [Customer] x
                 on x.No_ = a.CustomerNo
                 where x.No_ = b.No_ and 
                       c.No_ in('2101','2102','2103','2104','2105')
                       and Enrollmentdate < '2014-01-01'
                       and(a.Resignationdate < '1754-01-01 00:00:00.000' OR a.Resignationdate > '2014-12-31'))

Answer 1

Я обычно предпочитаю JOINs, чем INs, вы можете получить тот же результат, но движок может оптимизировать его лучше.

Вы присоединяете свой основной запрос (T1) к тому, что было подзапросом IN (T2), и фильтруете, что T2.ID имеет значение null, гарантируя, что вы не нашли ни одной записи, соответствующей этим условиям.

SELECT distinct T1.ID 
FROM Table T1 
     LEFT JOIN Table T2 on T2.ID = T1.ID AND 
                     T2.IteamNumber in (132,434,675) AND T2.DateCreated < '2019-01-01'
WHERE T1.IteamNumber in (132,434,675) AND Year(T1.DateCreated) = 2019 AND
      T2.ID is null

ОБНОВЛЕНИЕ: Вот предложение, обновленное вашим реальным запросом. Так как ваш подзапрос имеет внутренние объединения, я создал CTE, чтобы вы могли присоединиться к этому подзапросу. Функционирование такое же, вы оставили присоединение к своему основному запросу с подзапросом, и вы возвращаете только те строки, в которых не найдено подходящих записей.

with previous as (
  Select x.No_
  from [Line] c    
       inner join [Header] a on a.CollectionNo = c.CollectionNo
       inner join [Customer] x on x.No_ = a.CustomerNo
  where     c.No_ in ('2101','2102','2103','2104','2105')
        and Enrollmentdate < '2014-01-01'
        and (a.Resignationdate < '1754-01-01 00:00:00.000' OR a.Resignationdate > '2014-12-31'))
)
Select count(distinct b.No_),'2014'
from [Line] c    
     inner join [Header] a on a.CollectionNo = c.CollectionNo
     inner join [Customer] b on b.No_ = a.CustomerNo
     left join previous p on p.No_ = b.No_
where    c.No_ in ('2101','2102','2103','2104','2105')
     and year(Enrollmentdate)= 2014 
     and (a.Resignationdate < '1754-01-01 00:00:00.000' OR a.Resignationdate >= '2014-12-31')
     and p.No_ is null

Answer 2

Если я правильно понимаю, вы можете написать запрос в виде GROUP BY запрос с HAVING пункт:

SELECT ID 
FROM t
WHERE IteamNumber in (132, 434, 675)
GROUP BY ID
HAVING MIN(DateCreated) >= '20190101' -- no row earlier than 2019
AND    MIN(DateCreated) <  '20200101' -- at least one row less than 2020

Это удалит строки, для которых существует более ранняя запись. Вы можете еще больше повысить производительность, создав индекс покрытия:

CREATE INDEX IX_t_0001 ON t (ID) INCLUDE (IteamNumber, DateCreated)

Answer 3

Проблема из-за вашей IN Заявление, это предпочтительнее, по моему мнению, избегать каких-либо IN утверждение, а не это, создать join с подзапросом и отфильтровать ваши данные, используя where пункт.

В случае IN оператор каждой записи вашей таблицы сопоставляется со всеми записями подзапроса, что определенно замедляет ваш процесс.

Если это обязательно для использования IN затем использовать его с index, Создайте правильный индекс ваших уважаемых столбцов, которые улучшат вашу производительность.

Вместо IN вы можете использовать EXISTS повысить производительность вашего запроса.

Пример EXISTS является:

SELECT distinct ID 
FROM Table AS T 
WHERE IteamNumber in (132,434,675) AND Year(DateCreated) = 2019
      AND NOT EXISTS (
                     SELECT Distinct ID FROM Table AS T2 
                     WHERE T1.ID=T2.ID 
                     AND IteamNumber in (132,434,675) AND DateCreated < '2019-01-01' )