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

Спасибо конечно, но вы так и не ответили на вопрос. У меня в 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: да какая каша. Знаю я, что есть те, которые живут до конца транзакции, но не в коде C# транзакции. Там такое не прокатывает. Там в пределах одной транзакции, но в разных запросах не живёт временная таблица. Она живёт в пределах одного запроса, хоть эти запросы выполняются по очереди в одной транзакции. Пришлось использовать глобальную временную таблицу.

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

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

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

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