Который быстрее: СОЕДИНЕНИЕ с GROUP BY или Подзапросом?
Скажем, у нас есть две таблицы: 'Автомобиль' и 'Часть', с присоединяющейся таблицей в 'Car_Part'. Скажите, что я хочу видеть все автомобили, которые имеют часть 123 в них. Я мог сделать это:
SELECT Car.Col1, Car.Col2, Car.Col3
FROM Car
INNER JOIN Car_Part ON Car_Part.Car_Id = Car.Car_Id
WHERE Car_Part.Part_Id = @part_to_look_for
GROUP BY Car.Col1, Car.Col2, Car.Col3
Или я мог сделать это
SELECT Car.Col1, Car.Col2, Car.Col3
FROM Car
WHERE Car.Car_Id IN (SELECT Car_Id FROM Car_Part WHERE Part_Id = @part_to_look_for)
Теперь, все во мне хочет использовать первый метод, потому что я был воспитан хорошими родителями, кто привил мне пуританскую ненависть к подзапросам и любовь к теории множеств, но было предложено мне, чтобы выполнение той БОЛЬШОЙ ГРУППЫ было хуже, чем подзапрос.
Я должен указать, что мы находимся на SQL Server 2008. Я должен также сказать, что в действительности хочу выбрать базирующийся идентификатор Части, Тип детали и возможно другие вещи также. Так, запрос, который я хочу сделать на самом деле, похож на это:
SELECT Car.Col1, Car.Col2, Car.Col3
FROM Car
INNER JOIN Car_Part ON Car_Part.Car_Id = Car.Car_Id
INNER JOIN Part ON Part.Part_Id = Car_Part.Part_Id
WHERE (@part_Id IS NULL OR Car_Part.Part_Id = @part_Id)
AND (@part_type IS NULL OR Part.Part_Type = @part_type)
GROUP BY Car.Col1, Car.Col2, Car.Col3
Или...
SELECT Car.Col1, Car.Col2, Car.Col3
FROM Car
WHERE (@part_Id IS NULL OR Car.Car_Id IN (
SELECT Car_Id
FROM Car_Part
WHERE Part_Id = @part_Id))
AND (@part_type IS NULL OR Car.Car_Id IN (
SELECT Car_Id
FROM Car_Part
INNER JOIN Part ON Part.Part_Id = Car_Part.Part_Id
WHERE Part.Part_Type = @part_type))
3 ответа
У меня похожие данные, поэтому я проверил план выполнения для обоих стилей запроса. К моему удивлению, подзапрос Column In (CIS) создал план выполнения с на 25% меньшими затратами на ввод-вывод, чем запрос внутреннего соединения (IJ). В плане выполнения CIS я получаю 2 сканирования индекса промежуточной таблицы (Car_Part) по сравнению с сканированием индекса промежуточной и относительно более дорогостоящим хеш-соединением в IJ. Мои индексы исправны, но не кластеризованы, поэтому логично, что сканирование индекса может быть выполнено немного быстрее за счет их кластеризации. Я сомневаюсь, что это повлияет на стоимость хэш-соединения, которое является более дорогостоящим шагом в запросе IJ.
Как указывали другие, это зависит от ваших данных. Если вы работаете с большим количеством гигабайт в этих трех таблицах, отключитесь. Если ваши строки пронумерованы сотнями или тысячами, то вы можете сильно огорчиться из-за очень небольшого прироста производительности. Я бы сказал, что запрос IJ намного более читабелен, поэтому, если он достаточно хорош, сделайте одолжение любому будущему разработчику, который коснется вашего кода, и дайте ему что-нибудь более легкое для чтения. Количество строк в моих таблицах - 188877, 283912, 13054, и оба запроса вернулись за меньшее время, чем потребовалось, чтобы попить кофе.
Небольшой постскриптум: поскольку вы не агрегируете никакие числовые значения, похоже, вы хотите выбрать отдельные. Если вы на самом деле не собираетесь что-то делать с группой, легче увидеть свое намерение, выбрав в конце отдельные, а не сгруппировать. Стоимость ввода-вывода такая же, но один указывает на ваше намерение лучше ИМХО.
Лучшее, что вы можете сделать, - это протестировать их самостоятельно на реальных объемах данных. Это будет полезно не только для этого запроса, но и для всех будущих запросов, когда вы не уверены, какой из них лучший.
Важно сделать следующее:
- тест на объемах данных производственного уровня
- тестируйте честно и последовательно (очистите кеш: http://www.adathedev.co.uk/2010/02/would-you-like-sql-cache-with-that.html )
- проверьте план выполнения
Вы можете либо отслеживать с помощью SQL Profiler и проверять там продолжительность / чтения / записи / ЦП, либо УСТАНОВИТЬ СТАТИСТИКУ IO ON; SET STATISTICS TIME ON; для вывода статистики в SSMS. Затем сравните статистику по каждому запросу.
Если вы не можете провести этот тип тестирования, вы потенциально можете столкнуться с проблемами производительности в будущем, которые вам придется затем настроить / исправить. Существуют инструменты, которые вы можете использовать, которые будут генерировать данные за вас.
В SQL Server 2008 я ожидал, что В будет быстрее, поскольку он эквивалентен этому.
SELECT Car.Col1, Car.Col2, Car.Col3
FROM Car
WHERE EXISTS(SELECT * FROM Car_Part
WHERE Car_Part.Car_Id = Car.Car_Id
AND Car_Part.Part_Id = @part_to_look_for
)
т.е. ему нужно только проверить наличие строки, а не присоединяться к ней, а затем удалить дубликаты. Это обсуждается здесь .