Производительность табличных переменных

Сегодня хотел бы затронуть тему производительности переменных в виде табличек. В SQL Server можно создавать переменные, которые будут работать как целые таблицы. Возможно в других базах данных есть такие же возможности, я пользовался такими переменными только в MS SQL Server.

Итак, вы можете написать что-то типа: 

declare @t as table (int value)

Здесь мы объявляем переменю @t, которая будет представлять собой таблицу и будет состоять из одной колонки value типа число. Можно создавать более сложные таблицы, но для нашего примера в этой статье и для разговора про оптимизацию достаточно одной колонки. 

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

insert into @t

select UserID

from User

или

select * from @t

Я заметил, что табличные переменные используют, когда нужно выбрать данные для большой выборки. Например, у вас в коде есть запрос, который возвращает пользователей сайта. Теперь вы собираете все ID пользователей, помещаете в табличную переменную и можете искать все адреса для этих пользователей. Может быть кто-то скажет - что за бред и почему не выполнить одним запросом к базе и получить сразу все? У меня ростов слишком простой пример. Ну допустим, что пользователи приходят от Web сервиса, а адреса у вас в базе данных. Опа. Тут уже выхода нет. От сервиса мы получили кучу ID пользователей и чтобы для каждого из них н выполнять запрос к базе, кто-то решает, что проще засунуть все ID в параметр запроса в виде табличной переменной и запрос будет выглядеть круто:

select *

from @t as users 

   join Address a on a.UserID = users.UserID

Все это красиво работает, потом в коде C# вы можете слить результаты обоих  массивов данных в один объект. С помощью LiINQ это пишется за пять минут (хотя я видел далеко не эффективные реализации) и вроде бы никаких проблем.... Кроме производительности самого запроса. 

Дело в том, что переменные в виде таблиц не проектировались для того, чтобы обрабатывать большие данные. Если я не ошибаюсь, но оптимизатор запросов не будет заморачиваться, а все время будет использовать метод выполнения LOOP. То есть для каждого ID из @t будет происходить поиск в таблице Address. Если в @t находиться 1000 записей, то сервер 1000 раз будет сканировать Address. Я сейчас работаю над запросом, который выглядит примерно так же, но просто больше задействовано таблиц и у меня именно так и получилось. В плане выполнения нереальное количество сканов и сервер просто умирает в попытке найти данные. 

На много эффективнее взять просканировать всю таблицу Address и найти всех пользователей сразу. Этот метод называется MERGE, но его SQL Server в основном выбирает, когда данных очень много и когда они отсортированы. В данном случае оптимизатор понятия не имеет, какие данные будут в переменной, сколько их и тем более не представляет, есть ли сортировка, ведь на такой переменной нет индексов. 

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

Даже если просто заменить табличную переменную на SQL, это значительно увеличит скорость работы запроса:

select *

from (

 Select 10377 as UserID

 Union all

 Select 73736

 Union all

 Select 7474748

 ….

  ) as users 

   join Address a on a.UserID = users.UserID

Таких SELECT может быть даже тысяча и текст запроса будет огромным, но он будет выполняться в тысячи раз быстрее на большом количестве данных, потому что SQL Server сможет выбрать эффективный план выполнения. 

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

Неужели MS не подумали о нас? Подумали. Я думаю, они не ожидали, что пользователи будут так использовать табличные переменные, но красивый обходной манёвр есть. 

Есть несколько вариантов решения проблемы, но на мой взгляд самый эффективный с точки зрения производительности (из личного опыта, возможно я не прав) - добавить в конец запроса OPTION (RECOMPILE): 

select *

from @t as users 

   join Address a on a.UserID = users.UserID

OPTION (RECOMPILE)

Эта опция ставиться один раз в самом конце запроса, даже после ORDER BY, если он есть. Смысл этой опции в том, чтобы заставить SQL Server перекомпилировать запрос при каждом выполнении. 

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

В предыдущем абзаце описан и недостаток - план выполнения не сохраняется и серверу приходиться компилировать запрос каждый раз и искать эффективный план выполнения. Но я не видел таких запросов, в которых эта операция занимала больше 100 миллисекунд. Так что лучше 100 миллисекунд потерять, но потом быстро долететь, чем рано стартонуть, но потом долго и неэффективно ворочить данные. 

Плохо ли использовать переменные в виде таблиц? Нет. Используйте. Просто помните, что они не создавались для больших данных. Иногда лучше создать временную таблицу, если данных уж совсем много и поместить данные туда, может быть даже создать налету индекс. Мне приходилось делать такие вещи в случае с отчётами, правда всего один раз. Тогда я время генерации одного отчёта сократил на работе с 3 часов до 20 минут. 

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

Внимание!!! Если ты копируешь эту статью себе на сайт, то оставляй ссылку непосредственно на эту страницу. Спасибо за понимание