Выполнение «NOT IN» в SQL-запросе

Я довольно новичок в анализе SQL-запросов. Недавно я наткнулся на проблему производительности с одним из запросов, и мне интересно, правильно ли мой мыслительный процесс здесь и почему Query Optimizer работает так, как он работает в этом случае. Я om SQL Server 2012. У меня есть SQL-запрос, который выглядит как

SELECT * FROM T1 WHERE Id NOT IN (SELECT DISTINCT T1_Id from T2); 

Для запуска на моем тестовом сервере требуется около 30 секунд. Пытаясь понять, что происходит так долго, я переписал его, используя временную таблицу, например:

 SELECT DISTINCT T1_Id INTO #temp from T2; SELECT * FROM T1 WHERE Id NOT IN (SELECT T1_Id from #temp); 

Он работает в сто раз быстрее, чем первый.

Некоторая информация о таблицах: T2 имеет около 1 миллиона строк, и там имеется около 1000 различных значений T1_id. T1 имеет около 1000 строк. Первоначально у меня был только кластеризованный индекс на T2 в столбце, отличном от T1_Id, поэтому T1_id вообще не индексировался.

Рассматривая планы выполнения, я увидел, что для первого запроса было столько сканирований индексов, сколько есть значений T1_id, поэтому в этом случае SQL Server выполняет примерно 1000 сканирований индексов. Это заставило меня понять, что добавление некластеризованного индекса на T1_id может быть хорошей идеей (индекс должен был быть с самого начала, по общему признанию), и добавление индекса действительно заставило исходный запрос работать намного быстрее, поскольку теперь он некластерный индекс ищет.

Я ищу, чтобы понять поведение оптимизатора Query для исходного запроса – выглядит ли оно разумным? Есть ли способ заставить его работать так же, как и временный вариант таблицы, который я разместил здесь, а не делать несколько сканирований? Я просто что-то не понимаю? Заранее спасибо за любые ссылки на подобные обсуждения, так как я действительно ничего не нашел.

Оптимизатор SQL Server должен понимать размер, если таблицы для некоторых его решений.

При выполнении NOT IN с подзапросом эти оценки могут быть не совсем точными. Когда таблица фактически материализована, счет будет очень точным.

Я думаю, что первый будет быстрее с индексом на

 Table2(t1_id) 

Не в интуитивном, но медленном. Эта конструкция, как правило, работает быстрее.

 where id in (select id from t1 except select t1_id from t2) 

Фактическая производительность, вероятно, будет отличаться от оценок, но ни один из ваших запросов не выполнит этот запрос, что является стандартным стандартом де-факто:

 SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.Id = T2.T1_Id WHERE T2.T1_Id IS NULL 

Это использует правильное соединение, которое будет работать очень хорошо (при условии, что столбец внешнего ключа индексируется) и являющийся левым (внешним) соединением, условие WHERE выбирает только те строки из T1 которые не присоединяются (все столбцы правой таблицы равны null когда соединение промахивается).

Также обратите внимание, что DISTINCT не требуется, так как только одна строка возвращается из T1 для пропущенных объединений.

Это просто догадка, но, надеюсь, образованная …

СУБД, вероятно, пришли к выводу, что поиск большой таблицы в несколько раз быстрее, чем поиск маленькой таблицы много раз. Вот почему у вас было ~ 1000 запросов на T2 вместо ~ 1000000 запросов на T1 .

Когда вы добавили индекс в T2.T1_Id , это повернуло 1000 сканирований таблицы (или полное кластеризованное сканирование индексов, если таблица кластеризована) в ~ 1000 запросов индекса, что сделало вещи намного быстрее, как вы уже отметили.

Я не уверен, почему он не попытался присоединиться к хеш-соединению (или присоединиться к объединению после добавления индекса) – возможно, у него была устаревшая статистика и плохо переоценили количество различных значений?

Еще одна вещь: есть ли FOREIGN KEY на T2.T1_Id ссылаясь на T1.Id ? Я знаю, что Oracle может использовать FK для повышения точности оценок затрат (в этом случае он мог бы сделать вывод о том, что мощность T2.T1_Id не может быть больше T1.Id ). Если MS SQL Server делает что-то подобное, а FK отсутствует (или не доверено ), это может повлиять на мышление MS SQL Server, но есть более четкие значения, чем есть на самом деле.

(BTW, это помогло бы, если бы вы разместили фактические планы запросов и структуру базы данных.)

Давайте будем гением компьютера.