Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
Понедельник, 9 утра, сообщение в рабочем чате: "Всё сломалось, почините". Согласитесь, неприятная ситуация, особенно когда это ваш первый месяц работы, а сломалось что-то в функционале, с которым вы ещё ни разу не контактировали, да и не трогал его уже никто месяцами.
Так что случилось?
Сразу после просмотра логов всё становится ясно (нет): Timeout error от MS SQL сервера в модуле, который вызывается из клиентского приложения и обрабатывает документы за определённый период, переводит их в систему планирования и просчитывает различные временные и денежные показатели, после чего это всё становится доступным в другом модуле для различной оптимизации и логистики.
Так как тупо увеличить максимально возможное время выполнения процедур в настройках сервера это подход для слабаков, пришлось копать. Спустя несколько тестов и анализов планов выполнения, проблема найдена - справочник, получаемый в формате xml, записывается в табличную переменную. После очередного обновления статистики собрался новый план выполнения для процедуры, который максимально не эффективно джойнит эту переменную с основной выборкой. Моментальное решение - перейти на temp table, заменив символ @ на # в названии создаваемой таблицы (то что они по разному создаются в коде, одно через declare, другое через create, мы опускаем, чтобы не портить заголовок). Тестируем, получаем время выполнения в 1-2 секунды, радуемся, льём в прод, все счастливы (особенно я).
А в чём разница?
Пока нет никаких проблем и ситуация состоит в том, чтобы просто бахнуть куда-то нужные данные для дальнейшего использования в запросе, то разницы не будет. На просторах разных форумах можно наткнутся на вполне правильный совет - на больших данных использовать временную таблицу. Например на сайте майкрософта можно найти вот это:
No DDL is allowed on table variables. So if you have a large rowset which needs to be queried often, you may want to use #table when possible.
Но помимо отсутствия DDL, разница всё же более серьёзная:
#TABLE | @TABLE | |
Хранение в памяти | Хранится в tempdb | Хранится в tempdb, но имеет функционал передачи как переменная |
Возможность менять структуру после создания | Да | Нет |
Возможность создания индексов и ограничений | Да | Только первичный ключ в момент создания |
Доступ | Внутри сессии | Внутри пакета транзакций одной сессии |
Время жизни | Пока активна сессия | Пока действует один пакет транзакций |
Использование в функциях | Нет | Да |
Удобства с использовании | Можно создать и заполнить через команду select into | Не нужно проверять на наличие и удалять при перезапуске куска кода во время разработки |
Параллельность | Да | Нет |
Имеет статистику | Да | Нет |
В целом - это почти вся общая информация, которую легко найти, загуглив "ms sql temp table vs table variable", но понять причину конкретно моей ситуации это не помогает, ведь мне не пришлось создавать индексы или использовать прочие фокусы временных таблиц, достаточно было просто поменять 1 символ и всё.
Под капотом
Когда-то давно, вопрос про различие этих двух механизмов привёл меня на достаточно старый пост. Один из отрывков гласил:
Many of the execution plans involving table variables will show a single row estimated as the output from them. Inspecting the table variable properties shows that SQL Server believes the table variable has zero rows
However the results shown in the previous section do show an accurate
rowscount insys.partitions. The issue is that on most occasions the statements referencing table variables are compiled while the table is empty
И действительно, план выполнения процедуры с временной переменной гласил - скорее всего в вашей таблице 1 запись, так что запрос строим так, а не иначе. План выполнения был сгенерирован так, будто мы джойним таблицу всего с 1 строкой, так что можно не особо напрягаться за оптимизацию.
Этот момент особо нигде не расписан, и относится к разряду проблем "ну вот так вот сделано", но это никоим образом не говорит о проблемах продукта. В любом it молотке есть свои условности и особенности, с которыми можно столкнутся в одной из тысячи задач.
Заместо итога
На этом у меня пока всё. Это была моя первая попытка писательства, так-что прошу сильно не кидаться тапками за скупость языка или речевые обороты. Решил попробовать себя в этой стезе, так как услышал, что это помогает в обобщении своих знаний и поднятии навыков в целом. Спасибо за прочтение.
