SQL присоединяет подзапросы SQL (производительность)?

Я хочу знать, есть ли у меня запрос join что-то вроде этого -

Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id

и подзапрос что-то вроде этого -

Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept)

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

Также есть время, когда я должен предпочесть один над другим?

Извините, если это слишком тривиально и спрашивается раньше, но я смущен. Кроме того, было бы здорово, если вы, ребята, можете предложить мне инструменты, которые я должен использовать для измерения производительности двух запросов. Большое спасибо!

Ответ 1

Я бы ВЫИГРАЛ первый запрос быстрее, главным образом потому, что у вас есть эквивалентность и явный JOIN. По моему опыту IN является очень медленным оператором, поскольку SQL обычно оценивает его как серию предложений WHERE, разделенных символом "OR" (WHERE x=Y OR x=Z OR...).

Как и в случае с ALL THINGS SQL, ваш пробег может отличаться. Скорость будет сильно зависеть от индексов (у вас есть индексы на обоих столбцах идентификатора? Это многое поможет...) между прочим.

Единственный РЕАЛЬНЫЙ способ рассказать со 100% уверенностью, которая быстрее - включить отслеживание производительности (особенно полезная статистика IO) и запустить их обоих. Не забудьте очистить кеш между прогонами!

Ответ 2

Ну, я считаю, что это "старый, но золотой" вопрос. Ответ: "Это зависит!". Спектакли - настолько деликатная тема, что было бы слишком глупо говорить: "Никогда не используйте подзапросы, всегда присоединяйтесь". В следующих ссылках вы найдете несколько основных рекомендаций, которые я считаю очень полезными:

У меня есть таблица с 50000 элементами, в результате я искал 739 элементов.

Сначала мой запрос был таким:

SELECT  p.id,
    p.fixedId,
    p.azienda_id,
    p.categoria_id,
    p.linea,
    p.tipo,
    p.nome
FROM prodotto p
WHERE p.azienda_id = 2699 AND p.anno = (
    SELECT MAX(p2.anno) 
    FROM prodotto p2 
    WHERE p2.fixedId = p.fixedId 
)

и потребовалось 7,9 с.

Мой запрос наконец-то такой:

SELECT  p.id,
    p.fixedId,
    p.azienda_id,
    p.categoria_id,
    p.linea,
    p.tipo,
    p.nome
FROM prodotto p
WHERE p.azienda_id = 2699 AND (p.fixedId, p.anno) IN
(
    SELECT p2.fixedId, MAX(p2.anno)
    FROM prodotto p2
    WHERE p.azienda_id = p2.azienda_id
    GROUP BY p2.fixedId
)

и это заняло 0,0256 с

Хороший SQL, хорошо.

Ответ 3

Начните просматривать планы выполнения, чтобы увидеть различия в том, как SQl-сервер будет их интерпретировать. Вы также можете использовать Profiler для фактического запуска запросов несколько раз и получения разницы.

Я бы не ожидал, что они будут настолько ужасно разными, и вы сможете получить реальную прибыль. Большие выигрыши в производительности при использовании объединений вместо подзапросов - это когда вы используете коррелированные подзапросы.

EXISTS часто лучше, чем любой из этих двух, и когда вы говорите о левом соединении, где вы хотите, чтобы все записи не были в левой таблице соединений, то NOT EXISTS часто намного лучший выбор.

Ответ 4

Производительность основана на количестве данных, которые вы выполняете на...

Если меньше 20К. JOIN работает лучше.

Если данные больше похожи на 100k +, тогда IN работает лучше.

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

Все эти критерии, которые я тестировал, и таблицы имеют соответствующие индексы.

Ответ 5

Два запроса могут быть не семантически эквивалентными. Если сотрудник работает более чем в одном отделе (возможно, на предприятии, на котором я работаю, по общему признанию, это будет означать, что ваша таблица не полностью нормализована), тогда первый запрос будет возвращать повторяющиеся строки, тогда как второй запрос не будет. Чтобы сделать запросы эквивалентными в этом случае, ключевое слово DISTINCT должно быть добавлено в предложение SELECT, что может повлиять на производительность.

Обратите внимание, что существует правило проектирования, в котором указано, что таблица должна моделировать сущность/класс или отношения между объектами/классами, но не обе. Поэтому я предлагаю вам создать третью таблицу, скажем OrgChart, чтобы моделировать отношения между сотрудниками и отделами.

Ответ 6

Производительность должна быть одинаковой; гораздо важнее иметь правильные индексы и кластеризацию, применяемые к вашим таблицам (существуют некоторые хорошие ресурсы по этой теме).

(Отредактировано с учетом обновленного вопроса)

Ответ 7

Я знаю, что это старый пост, но я думаю, что это очень важная тема, особенно в наши дни, когда у нас есть записи 10M+ и мы говорим о терабайтах данных.

Я также расскажу о следующих наблюдениях. У меня около 45 миллионов записей в моей таблице ([данные]) и около 300 записей в моей таблице [кошки]. У меня есть обширная индексация для всех запросов, о которых я собираюсь поговорить.

Рассмотрим пример 1:

UPDATE d set category = c.categoryname
FROM [data] d
JOIN [cats] c on c.id = d.catid

против примера 2:

UPDATE d set category = (SELECT TOP(1) c.categoryname FROM [cats] c where c.id = d.catid)
FROM [data] d

Пример 1 занял около 23 минут. Пример 2 занял около 5 минут.

Поэтому я бы пришел к выводу, что подзапрос в этом случае гораздо быстрее. Конечно, имейте в виду, что я использую SSD-накопители M.2, способные работать со скоростью ввода/вывода @1 ГБ/с (то есть, байты, а не биты), поэтому мои индексы тоже очень быстрые. Так что это может повлиять и на скорость в ваших обстоятельствах

Если это однократная очистка данных, возможно, лучше просто оставить ее запущенной и завершенной. Я использую TOP (10000) и вижу, сколько времени потребуется, и умножаю его на количество записей, прежде чем нажимать на большой запрос.

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

Ответ 8

Вы можете использовать объяснительный план, чтобы получить объективный ответ.

Для вашей проблемы фильтр Exists, вероятно, выполнит самую быструю операцию.

Ответ 9

Конечный запрос включал azienda_id в обработанном подзапросе, но ваш первоначальный запрос не включал azienda_id в завершенный подзапрос. Таким образом, сравнение не является таким же.

Ответ 10

Я тестировал теорию HLGEM, сравнивая числа "статистики использования клиентов", оказывается, что она не существует быстрее, чем левое соединение при поиске всех записей, не находящихся в левой таблице.

Красота SQL - это множество способов ее написания, а производительность зависит не только от объединения или подзапроса, но и от того, что вы ищете.