Правда ли, что в MS SQL Server создавать временные таблицы лучше вне транзакции?

Question

[Борис Животное]

То есть сначала создали, а потом уже используем её в транзакции? Я вычитал, что если создавать временную таблицу в транзакции, то блокируются какие-то системные таблицы на время выполнения этой моей транзакции. Это правда? Где-то есть в источниках Microsoft инфа об этом? Я вычитал тут

Comments

[Алексей]

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

Создавать и заполнять времянки лучше до открытия транзакции. По хорошему получили данные, проверили и только после этого записываем.

Насчет блокировки - никогда не слышал про такое.

[shagguboy]

А просто посмотреть блокировки в сиквеле самому - не судьба?

[Борис Животное]

shagguboy: точно. Судьба. Не помню, как это делается. Буду гуглить :). Бывает очевидные вещи не приходят в голову из-за того, что голова занята много чем ещё.

[Борис Животное]

Алексей: а почему лучше? В итоге пишут некоторые люди, что это неправда, но создавать временные таблицы всё-таки лучше до транзакции.

[shagguboy]

Борис Животное: sp_who2

[Алексей]

Борис Животное: потому что если есть возможность избежать падение транзакции, то лучше это сделать. Плюс я считаю что шарповая транзакция это зло, которое нужно по возможности избегать. Они не надежны и сложно отлаживаемы.

[Борис Животное]

Алексей: как сделать по другому я не знаю. Я имею ввиду так, чтобы и одни запросом, да ещё и без шарповой транзакции. А если исключение в коде C# произойдёт? Тогда как быть? Если у меня несколько обращений к БД из кода шарпа в рамках одной транзакции должно быть, то тут без вариантов нужно юзать класс для работы с транзакцией из ADO.NET. И в том проекте, над которым работаю, тут просто всюду эти транзакции. Не знаю в чём там зло, всё работает, как я того ожидаю. Проблем не видел.

Answer 1

[Дмитрий Ковальский]

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

Для одиночных инструкций(одного запроса SELECT,INSERT,UPDATE,DELETE) существует альтернатива - Обобщенные табличные выражения(CTE) - link

Разумеется если временная таблица вам нужна больше чем на 1 запрос - такой подход неприменим.

Comments

[Борис Животное]

Спасибо конечно, но вы так и не ответили на вопрос. У меня в C# коде создана транзакция и выполняются 3 запроса в этой транзакции. Пришлось создать глобальную временную таблицу, чтобы из C# кода получив в первом запросе данные не вызывать в цикле для каждой полученной записи получение записи из дочерней таблицы по ID записи родителя (каждая запись, полученная в первом запросе). Это как альтернатива тройному JOIN. Если у меня в БД 1 запись в основной таблице (родитель) и по 10 записей в двух дочерних таблицах, то соединив я получу на выходе 100 записей, а не 20, как хотелось бы.

[Борис Животное]

Временную таблицу я создаю и именую её уникально в каждом вызове метода класса в C# коде, который работает с БД. То есть GetData() - создаётся временная таблица с уникальным именем. Раньше она создавалась в транзакции. Сейчас я вынес из транзакции. Если возникнет исключение, то она не удалится, как раньше. А удалится, когда я отключусь от БД.

[Дмитрий Ковальский]

Борис Животное: Моя личная рекомендация - не ваяйте такие конструкции ради взаимодействия с базой. Напишите хранимку, в которой выполните ряд инструкций в рамках транзакции. Колхозить транзакцию на уровне шарпного кода, потом ее откатывать - это много головной боли, разбросанной по местам, где на мой взгляд ее быть не должно.

[Борис Животное]

Дмитрий Ковальский: мне нужно получить из БД записи из основной таблицы и все дочерние для каждой записи из основной таблицы. Сделать одним запросом можно это с помощью LEFT OUTER JOIN с двумя таблицами. Как писал выше, получается огромное кол-во дублирующихся записей. А если добавить ещё одну таблицу, то там вообще мега много строк выдает запрос. В итоге пришлось сделать 3 запроса, первый получает данные из основной таблицы и кладёт айдишники полученных записей во временную таблицу. Второй запрос получает все дочерние записи для каждого айдишника из временной таблицы, третий так же. А как по другому мне получив данные заполнить ими экземпляры C# классов?
Вот в этом вопросе более-менее понятно описано, что я хочу сделать. И мой же ответ, как это я решил. Опасно ли выполнять такой код в С# + T-SQL? Или каким способом можно написать по-другому?

[Дмитрий Ковальский]

Борис Животное: Чисто для примера:

WITH one (x)
AS (SELECT 1 AS x),
two (y)
AS (SELECT x+1 AS y FROM one),
three (z)
AS (SELECT y+1 AS z FROM two)

SELECT what your need from three
where some condition

Тут можно к последнему запросу джойнить one и two по каким либо признакам. Инструмент очень хороший и многие задачи упрощает и делает прозрачнее.

[shagguboy]

Каша в голове. Временные таблицы бывают разные. Есть такие которые живут только до конца транзакции

[Борис Животное]

shagguboy: да какая каша. Знаю я, что есть те, которые живут до конца транзакции, но не в коде C# транзакции. Там такое не прокатывает. Там в пределах одной транзакции, но в разных запросах не живёт временная таблица. Она живёт в пределах одного запроса, хоть эти запросы выполняются по очереди в одной транзакции. Пришлось использовать глобальную временную таблицу.

Answer 2

[Максим Кузнецов]

Есть тонкость - распределённые транзакции. В MS SQL Server происходит эскалация транзакции даже в том случае, если в рамках транзакции идёт обращение к двум разным БД на одном сервере. Поскольку временные таблицы создаются в отдельной БД, при некоторых условиях могут возникать проблемы с MSDTC. Эскалация была значительной проблемой в 2005-й версии, но мы не так давно сталкивались с ней на 2008-й.

Comments

[Борис Животное]

В итоге мне вынести создание временной таблицы из транзакции в отдельный запрос? Или никакого смысла?

[Максим Кузнецов]

Есть две общих рекомендации, которые мы для себя вынесли из нашей ситуации: максимально упрощать транзакцию и стараться избегать обращений к разным источникам в рамках одной транзакции.
Наша проблема была связана с тем, что в рамках TransactionScope шло сохранение данных в операционную БД и одновременное логирование этого процесса в БД мониторинга, причём обе базы были на одном инстансе. Мы переработали реализацию в соответствии с указанными , что решило проблему.
Замечу, что эскалация происходила при записи данных в две разные БД в рамках одной довольно тяжёлой транзакции: в операционную БД сохранялся большой объём данных в разные таблицы. Ключевым показателем тут является производительность. Нет возможности точно сказать, в какой момент СУБД решает, что нужно эскалировать транзакцию. Но лёгкие транзакции, даже при записи в две разные базы, проходят без эскалации. Тоже самое можно сказать о чтении данных из операционной БД при одновременной записи журнала.
Вероятно, если Вы создадите временную таблицу в рамках одной транзакции, а прочитаете данные из неё и используете их в другой, то эскалации не будет. Но 100% гарантии это не даст. Решение всё равно принимает СУБД.

[Захар]

Максим Кузнецов:
>Нет возможности точно сказать, в какой момент СУБД решает, что нужно эскалировать транзакцию
есть довольно точное определение: СУБД решает, что нужно эскалировать транзакцию, когда затраты памяти на блокировки превышают установленный порог памяти (по умолчанию для ms sql это 24% от памяти, выделенной database engine). подробности можно найти в документации MS SQL в разделе "Пороги укрупнения блокировки"

[Максим Кузнецов]

Захар: Вы про это: "Если параметр locks имеет значение по умолчанию 0, порог укрупнения блокировок достигается, если память, используемая объектами блокировки, составляет 24 % от памяти компонента Database Engine, исключая память AWE"? Но это про блокировки, а я говорю про эскалацию транзакции. Это разные вещи.

Answer 3

[shagguboy]

Нет это не правда, соответствено на сайте майкрософта не найдешь

Answer 4

[Артём Токаревских]

Ни разу не специалист по SQL (потому как fool stack developer), так что воспринимайте моё мнение критично. Но вопрос заинтересовал.

Не вижу причины отказываться от транзакций в C# коде, если это легаси проект. Наверняка там есть ещё чем заняться. Сам старался не использовать, хотя бывало. Зачастую, оборачивал группу хранимых процедур хранимкой с транзакцией, которую и вызывал из C#.

Зачем тащить создание временной таблицы в транзакцию? Из-за боязни, что она не очистится? Можно сделать пару хранимок создания и удаления глобальных временных таблиц с проверкой существования, которые вызывать до и после транзакции. Всегда придерживался точки зрения — чем меньше кода в транзакции, тем лучше.

Временные таблицы стараюсь не использовать. Хотя если объём временных данных большой и нужны индексы, почему нет. Бывало, что злоупотребление ими серьёзно просаживало производительность, бывало и наоборот. Недавно надо было предавать данные между базами одного инстанса. Сначала использовал временные таблицы. Потом, по совету архитектора, переписал и стал пересылать через xml параметр (там это хорошо вписывалось в общую канву).