Один из лучших способов повысить эффективность запросов JOIN - ограничить количество записей требуемых для соединения. Возвращайте только те записи, которые будут объединяться, не более того.
Если таблица часто используется в запросах JOIN, имеет смысл создать для каждого соединяемого столбца свой собственный индекс. Вообще говоря, для оптимальной производительности запроса JOIN лучше использовать кластерный ключ чем некластерный. Если эти поля по какой либо причине имеют большой размер, массивны, то можно добавить суррогатный ключ в таблицу. Например, целочисленное инкрементное поле. Это приведет к уменьшению размера ключа, снижению операций ввода-вывода во время процесса объединения, увеличению общей производительности.
Помните, если вы создаете внешний ключ, то индекс не создается автоматически в то же время. Если вы планируете объединять таблицы при помощи внешних ключей, то необходимо добавить индекс к полю внешнего ключа. Такой индекс может в значительной степени увеличить эффективность соединений.
Избегайте соединять таблицы по полям с избыточными, повторяющимися значениями. Дело в том, что оптимизатор запросов SQL сервера в этом случае может не использовать существующий индекс для ускорения объединения данных. Для лучшей производительности запросы JOIN необходимо делать по полям, которые имеют уникальные индексы.
Для лучшей эффективности запросов JOIN индексы по соединяемым полям должны быть числового типа, а не CHAR или VARCHAR или другого нечислового типа данных. Это уменьшит стоимость запросов и увеличит их производительность.
Для максимальной производительности при соединении таблиц индексы по объединяемым полям должны быть одно и того же типа данных и иметь одну и ту де длину. Это также означает, что вы не должны объединять поля с типами данных Unicode и не-Unicode. Например, VARCHAR и NVARCHAR. Серверу придется неявно производить преобразование данных для объединения, что несомненно вызовет замедление выполнения запроса. Кроме того, в данном случае оптимизатор запросов может не использовать существующие индексы, а вместо этого производить сканирование таблицы.
Если для получение необходимого набора данных часто приходится объединять четыре или более таблиц, рассмотрите возможность денормализации данных. Часто добавление одного или двух полей из одной таблицы к другой может уменьшить количество объединений и значительно увеличить эффективность запросов.
Если ваш запрос JOIN работает медленно и использует HINTS, попробуйте убрать эти хинты. Может быть оптимизатор запросов выполнит работу по объединению лучше чем вы думали. Это особенно важно если ваше приложение перешло с версии 6.5 на 7.0, или с 7.0 на 2000.
Для очень больших и тяжелых запросов на соединение расположите таблицы в разные физические файлы в одной файловой группе filegroup. Это позволит серверу создавать раздельные потоки для каждого файла, что приведет к увеличению производительности.
Не используйте кроссовые объединения, за исключением тех случаев, когда это единственный вариант чтобы добиться нужного результата. Некоторые разработчики объединяют таблицы используя CROSS JOIN, затем накладывают DISTINCT или GROUP BY чтобы "почистить" всю получившуюся кучу данных. Это, как вы понимаете, приводит к огромным затратам системных ресурсов сервера.
Если вам нужно выбрать что использовать - JOIN или подзапрос для выполнения своей задачи, то выбирайте JOIN (часто используется объединения OUTER JOIN). Это обычно быстрее, но не всегда. Например, если возвращаемый набор данных мал или на объединяющих полях нет индексов, тогда подзапрос может быть быстрее. Единственные способ узнать наверняка - попробовать оба метода и изучить их планы исполнения. Для часто используемых запросов есть смысл потратить время и выбрать наиболее эффективный способ.
Допустим, у нас есть запрос, где в части SELECT содержатся два подзапроса включающих агрегатную функцию (Sum, Count и т.д.). Запрос выполнялся медленно. Нам удалось определить проблему - это использование агрегатных функций в подзапросе. Для устранения проблемы мы организовали запрос таким образом, что он по прежнему содержал агрегатную функцию в части SELECT, но заменили подзапросы серией объединений JOIN. Такой запрос выполнялся намного быстрее. Поэтому, лучше использовать соединения JOIN вместо подзапросов, когда они содержат агрегатные функции.