Производительность временных таблиц у SQL Server


5 0

У меня на работе ETL скрипты - это просто SQL файлы, которые мы запускаем на сервере в определенном порядке. Все работало прекрасно, пока один скрипт не начал тормозить капитально. Общая скорость работы скрипта стала превышать лимит в 3 часа. 

Я запустил скрипт пошагово и он выглядел вполне нормально. Вначале создается таблица:

declare @promotions table (PromotionID int, Здесь есть еще колонки);

Потом таблица заполняется данными и если выполнить скрипт заполнения данными, то он работает несколько секунд. 

Потом выполняется огромный скрипт, где используется эта временная табличка. И вот тут скрипт умирает. Я по плану выполнения нашел пару слабых мест и создал два индекса. У меня локально все работало идеально быстро. Пустил все в прод и никаких изменений. 

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

1. Объявнить переменные и таблицу

2. Заполнить таблицу @promotions

3. Выполнить большой скрипт, который копирует подготовленные данные во временную таблицу ##calcs. При этом в расчете участвует @promotions

4. Убить две таблицы на сервере отчетности

5. Заполнить таблицы данными из ##calcs

Когда я проверял пошагово, то я изменил имя таблицы @promotions на #promotions, чтобы после выполнения второго шага данные сохранились во временной таблице и я мог после паузы продолжить выполнение. И я не видел проблемы. Потом я решил сделать это изменение на постоянку и запустил запрос полностью. Простая замена @promotions на #promotions ускорила выполнение с таймаута до 2 минут. 

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

Надо разбираться, возможно это где-то какие-то настройки. Я не вижу причин, почему переменная может так сильно тормозить на SQL Server сервере. Не должно быть такой глобальной разницы. Я в принципе табличные переменные использую очень и очень редко, но все равно, интересный клинический случай. Для обработки данных я всегда использовал временные таблицы (скрипт, который я описал выше написан был не мной), теперь буду больше внимания уделять производительности табличных переменных. 


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


Комментарии

Phosgen

15 Июля 2013

Кстати, какую версию SQL сервера используете?(По проблеме ничего не могу сказать, просто интересно).


iAlex

15 Июля 2013

Я тоже ни так давно заметил такую же проблему, хотя в теории должно быть одно и тоже


Михаил Фленов

15 Июля 2013

2Phosgen
2008-ю версию

2iAlex
Разница может быть, но не такая глобальная.


silver

16 Июля 2013

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


Михаил Фленов

16 Июля 2013

Не помню, чтобы я где-то передавал таблицы в хранимки, но табличные переменные использую только для описания возвращаемых значений для хранимых функций. Для хранилищ никогда не использовал.

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


Добавить Комментарий

Еще что-нибудь

Хотите найти еще что-то интересное почитать? Можно попробовать отфильтровать заметки на блоге по категориям.

О блоге

Программист, автор нескольких книг серии глазами хакера и просто блогер. Интересуюсь безопасностью, хотя хакером себя не считаю

Обратная связь

Без проблем вступаю в неразборчивые разговоры по e-mail. Стараюсь отвечать на письма всех читателей вне зависимости от страны проживания, вероисповедания, на русском или английском языке.

Пишите мне