Как повлиять на план выполнения запроса базы данных?

03 Мая 2016 Программирование
3 0

Очередной праздник для твоего мозга - вторая заметка про программирование. Хот эта была написана до статьи, которую я опубликовал недавно, но именно такой порядок чтения и публикации имеет больше смысла. 

Я как-то писал про существование подсказок компилятору в виде loop join, merge join или hash join, которые позволяют заставить SQL Server выбрать определённый план выполнения, об этом здесь - Подсказки оптимизатору MS SQL Serever. Я заметил, что не так уж и много народа знает о существовании этих подсказок, оно и к лучшему. В рабочем приложении нужно доверяться SQL Server ведь план выполнения может зависеть от количества данных, для малого количества лучше выполнить loop, а для большого merge. 

Допустим, что у тебя связывается две таблицы: 

Select *

From Table1 t1

 left join Table2 t2 on t1.key =t2.key

Where …..

Это практически псевдокод, написанный реально на коленке (пишу заметку на iPad-е, который лежит на ногах), так что не нужно доебываться к именованию и синтаксису. Если количество данных из t1 мало, то выгодно прогнать поиск по этой таблице и потом для каждой строки в результате запустить поиск по t2. Такой join называют loop и это то же самое, что написать: 

Select *

From Table1 t1

 left LOOP join Table2 t2 on t1.key =t2.key

Where …..

Все прекрасно работает, пока t1 не вернёт кучу данных. Если он вернёт 1,000 строк, то придётся сканировать t2 тысячу раз. Получится как в сказках Шахиризады 1001 ночь - тысяча и один скан. И тут стоит молить бога клавиатуры, создавал схему, что он создал индекс на нужные ключи, иначе поиск может убить базу данных. В этом случае проще запустить поиск по t1 и одновременно найти возможные кандидаты в t2, а потом обьединить результат. Такой способ поиска называется merge:

Select *

From Table1 t1

 left MERGE join Table2 t2 on t1.key =t2.key

Where …..

В этом случае идёт только по одному сканированию по каждой из таблиц в поисках данных, а потом результат объедается в памяти.  Такой вариант выгоден, когда с обоих сторон много данных.

Если у сервера статистика впрядите и есть индексы, то SQL Server обычно хорошо выполняет запросы и правильно выбирает, когда делать merge, а когда loop. Но у меня на работе очень много запросов, типа:

Select *

From Table1 t1

 left join VIew t2 on t1.key =t2.key

Where …..

У меня происходит связь с представлением View, которое достаточно навороченное и в нем куча связей. Из t1 так же выбирается много данных. Индексы все есть, статистику обновил, но SQL Serve все равно тупит и пытается выполнять LOOP JOIN, который выполняется по несколько минут, хотя если я ставлю подсказку MERGE JOIN, запрос выполняется секунды. 

Но merge join нельзя использовать в рабочем коде, потому что у него есть один недостаток - если из t1 не будет выбрано ни одной строки (результат NULL), сервер не сможет выполнить операцию MERGE и валиться с ошибкой и требует выполнять запрос без подсказок, чтобы оптимизатор смог выбрать тип LOOP JOIN. 

Возможно это какая-то проблема архитектуры, то так классно было бы, чтобы подсказка MERGE (а она даже в документации стоит как подсказка HINT, а не требование) была именно подсказкой. Если сервер не может выполнить merge, то выбирай то, что посчитаешь нужным, но не падай. Это было бы просто супер в моем случае, но я чувствую, что Microsoft никогда не решиться реализовать что-то подобное. Если сделать что-то такое, то все программисты начнут использовать подсказки на рабочих серверах и будут путаться выглядеть умнее SQL Server. 

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

Я пока нашёл одно решение для себя: создать табличную перемётную и сохранить результат выполнения VIEW в ней, а потом производить join на эту переменную. По умолчанию SQL Server при выполнении join с табличными переменными так же использует LOOP, но если в конец запроса добавить OPTION (RECOMPILE), то сервер видит большое количество данных в переменой и производить merge. О производитености табличных переменных я писал здесь.

Да, такой подход мягко говоря не очень хороший, но только так я смог заставить сервер делать обьединение данных и время выполнения запроса упало с нескольких минут до нескольких секунд. Сервер категорически отказывался выбирать правильный план выполнения, а MERGE JOIN я использовать не мог, потому что теоретически приложение может запросить данные, при которых в результате может ничего не вернуться и тогда приложение упадет. Я посчитал, что лучше уж использовать табличную переменную. 

Понравилось? Кликни Лайк, чтобы я знал, какой контент более интересен читателям. Заметку пока еще никто не лайкал и ты можешь быть первым