Конкатенация SQL, заполняющая базу данных tempDB
Мы пытаемся объединить, возможно, тысячи строк текста в SQL с помощью одного запроса. Запрос, который у нас сейчас есть, выглядит так:
DECLARE @concatText NVARCHAR(MAX)
SET @concatText = ''
UPDATE TOP (SELECT MAX(PageNumber) + 1 FROM #OrderedPages) [#OrderedPages]
SET @concatText = @concatText + [ColumnText] + '
'
WHERE (RTRIM(LTRIM([ColumnText])) != '')
Это работает отлично с функциональной точки зрения. Единственная проблема, с которой мы сталкиваемся, это то, что иногда ColumnText может иметь длину несколько килобайт. В результате мы заполняем базу данных tempDB, когда у нас есть тысячи этих строк.
Лучшая причина, по которой мы пришли, заключается в том, что, когда мы делаем эти обновления для @concatText, SQL использует неявные транзакции, поэтому строки эффективно неизменяемы.
Мы пытаемся найти хороший способ решения этой проблемы, и пока у нас есть два возможных решения: 1) Выполнить конкатенацию в.NET. Это хороший вариант, но это много данных, которые могут вернуться по проводам.
2) Используйте.WRITE, который работает аналогично методу.NET String.Join. Я не могу понять синтаксис для этого, поскольку BoL не покрывает этот уровень махинаций SQL.
Это приводит меня к вопросу: будет ли.WRITE работать? Если так, какой синтаксис? Если нет, есть ли другие способы сделать это без отправки данных в.NET? Мы не можем использовать FOR XML потому что наш текст может содержать недопустимые символы XML.
Заранее спасибо.
4 ответа
Я бы посмотрел на использование интеграции CLR, как это предлагается в комментарии @Martin. Агрегатная функция CLR может быть просто билетом.
Что именно заполняет базу данных? Не может быть @concatText = @concatText + [ColumnText], здесь нет никакой неизменности, и переменная @concatText будет в худшем случае иметь размер 2 ГБ (я ожидаю, что ваша база данных tempdb будет намного больше, если не увеличит ее). Похоже, ваш план запросов создает катушку для защиты haloween, и эта катушка является виновником.
В качестве общего ответа, используя UPDATE ... SET @var = @var + ... для конкатенации, как известно, есть проблемы с корректностью и не поддерживается. Альтернативные подходы, которые работают более надежно, обсуждаются в разделе Конкатенация значений строк в Transact-SQL.
Согласовано... Пользовательская функция CLR будет лучшим подходом к тому, что вы, ребята, делаете. Вы могли бы фактически прочитать текстовые значения в объект, а затем соединить их все вместе (внутри CLR) и заставить функцию выплевывать результат NVARCHAR(MAX). Если вам нужны подробности о том, как это сделать, дайте мне знать.
Во-первых, из вашего поста не ясно, нужны ли вам временные таблицы. Конкатенация может быть встроена в запрос. Если вы покажете нам больше о запросе, который заполняет базу данных tempdb, мы можем помочь вам переписать его. Во-вторых, вариант, который не был упомянут, состоит в том, чтобы полностью выполнять манипуляции со строками вне T-SQL. Т.е. в вашем запросе среднего уровня для необработанных данных выполните манипуляцию и отправьте ее обратно в базу данных. Наконец, вы можете использовать Xml так, чтобы результаты правильно обрабатывали экранирование и сущности. Опять же, нам нужно знать больше о том, что и как вы пытаетесь достичь.