Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Конкатенация большого количества значений  [new]
Big17
Member

Откуда:
Сообщений: 675
Коллеги, есть проблема производительности при конкатенации значений в переменную nvarchar(max)

Вот смотрите:
	DECLARE @i int = 0
	DECLARE @Text nvarchar(max) = ''

	WHILE (@i < 10000)
	BEGIN
		SET @i = @i + 1
		SET @Text += 'ffffffffffffffffffffffffffffff'
	END


Если в строчке с присвоением внутри цикла указывать не 'ffffffffffffffffffffffffffffff', а 'ffff' (т.е. просто меньшее кол-во символов) - то производительность запроса изменяется в разы. То есть явное линейное падение производительности.

Это что же, сиквел при такой конкатенации вытаскивает все в память, выполняет операцию, а затем опять скидывает в кэш/на диск.
Т.е. не просто добавляет нужные символы к уже висящей в памяти переменной?

Собственно вопроса два:
1. Почему так происходит? (можете просто ссылкой кинуться в меня)
2. А есть ли альтернативные способы такой конкатенации?
28 мар 18, 11:43    [21292635]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20533
Ну в данном конкретном случае чего бы не попользовать REPLICATE()...
28 мар 18, 11:50    [21292671]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
Big17
Member

Откуда:
Сообщений: 675
Дополнение: varchar(max) работает в разы быстрее nvarchar(max)
а nvarchar(4000) в десятки раз быстрее nvarchar(max)

Но мне нужна длинная строчка ((( порядка около 500 тыс. символов по итогу
28 мар 18, 11:51    [21292674]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
Big17
Member

Откуда:
Сообщений: 675
Akina
Ну в данном конкретном случае чего бы не попользовать REPLICATE()...

Да нет, в реальном то запросе там разные значения будут...
Это я для экспериментов в чистых условиях такой запрос наваял.
28 мар 18, 11:52    [21292682]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Тогда покажите все условия чтобы планируете реализовать через такую логику. Может там цикл и не нужен будет
28 мар 18, 12:02    [21292735]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
Гигабайт Мегабайтович Килобайтов
Member [заблокирован]

Откуда:
Сообщений: 5975
вы изучали какой нибудь язык программирования, который "в ручную" работает с памятью? ))
понимаете чем отличается char,nvarchar типы?
эти вопросы для того что, бы понять куда вас "ткнуть пальцем" ))
28 мар 18, 12:06    [21292748]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
Big17
Member

Откуда:
Сообщений: 675
Да, я знаю особенности и различия char/nchar/varchar/nvarchar/max...

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

Условия которые не обойти:
- не уложится в 4000 или в 8000 символов (даже внутри каждой итерации)


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

	SET NOCOUNT ON

	DECLARE @i int = 0
	DECLARE @SqlText nvarchar(max) = ''

	DECLARE @MyTableVar table(SqlText nvarchar(max))


	WHILE (@i < 10000)
	BEGIN

		SET @i = @i + 1

		SET @SqlText = 'ffffffffffffffffffffffffffffff'

		INSERT INTO @MyTableVar (SqlText)
		VALUES (@SqlText)

	END


	 SELECT @SqlText = STUFF((SELECT '' + [SqlText]
	  FROM @MyTableVar
	   for xml path('')) ,1,1, '' )

   SELECT LEN(@SqlText)


Работает заметно быстрее, даже на значительно больших количествах строк.
28 мар 18, 12:13    [21292770]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
iap
Member

Откуда: Москва
Сообщений: 46978
DECLARE @Text nvarchar(max) = N'';
SET @Text=REPLICATE(CAST(N'ffffffffffffffffffffffffffffff' AS NVARCHAR(MAX)),10000);


P.S. Если не хотите писать N перед строками, то не используйте юникодный тип.
28 мар 18, 12:14    [21292776]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
iap
Member

Откуда: Москва
Сообщений: 46978
Big17
Да, я знаю особенности и различия char/nchar/varchar/nvarchar/max...

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

Условия которые не обойти:
- не уложится в 4000 или в 8000 символов (даже внутри каждой итерации)


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

	SET NOCOUNT ON

	DECLARE @i int = 0
	DECLARE @SqlText nvarchar(max) = ''

	DECLARE @MyTableVar table(SqlText nvarchar(max))


	WHILE (@i < 10000)
	BEGIN

		SET @i = @i + 1

		SET @SqlText = 'ffffffffffffffffffffffffffffff'

		INSERT INTO @MyTableVar (SqlText)
		VALUES (@SqlText)

	END


	 SELECT @SqlText = STUFF((SELECT '' + [SqlText]
	  FROM @MyTableVar
	   for xml path('')) ,1,1, '' )

   SELECT LEN(@SqlText)



Работает заметно быстрее, даже на значительно больших количествах строк.
А зачем удаляете первый символ?
Вы же перед строкой ничего не добавляете!
28 мар 18, 12:18    [21292789]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Big17
[src]В реальной задаче у меня конечно же не цикл, а курсор, который формирует итоговый набор данных.
Но просадка по производительности именно в конкатенации.

Покажите... ибо не понятна суть задачи что вы решаете. То что написано решается через REPLICATE. А делать замеры скорости дело неблагодарное для абстрактных задач.
28 мар 18, 12:19    [21292792]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
Big17
Member

Откуда:
Сообщений: 675
N перед литералами практически не влияет на производительность в моем случае
28 мар 18, 12:19    [21292794]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
Big17
Member

Откуда:
Сообщений: 675
AlanDenton
Big17
[src]В реальной задаче у меня конечно же не цикл, а курсор, который формирует итоговый набор данных.
Но просадка по производительности именно в конкатенации.

Покажите... ибо не понятна суть задачи что вы решаете. То что написано решается через REPLICATE. А делать замеры скорости дело неблагодарное для абстрактных задач.



Да нет, в реальном то запросе там разные значения будут...
Это я для экспериментов в чистых условиях такой запрос наваял.



В реальной задаче у меня конечно же не цикл, а курсор, который формирует итоговый набор данных.
28 мар 18, 12:20    [21292797]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
iap
Member

Откуда: Москва
Сообщений: 46978
Big17
N перед литералами практически не влияет на производительность в моем случае
Влияет на корректность преобразования VARCHAR() в NVARCHAR().
При чём здесь производительность?
28 мар 18, 12:22    [21292806]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
iap
Member

Откуда: Москва
Сообщений: 46978
Big17
В реальной задаче у меня конечно же не цикл, а курсор, который формирует итоговый набор данных.
А по курсору-то циклом, небось, гуляете?
Прямой связи между циклом и курсором нет.
28 мар 18, 12:24    [21292811]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
Гигабайт Мегабайтович Килобайтов
Member [заблокирован]

Откуда:
Сообщений: 5975
Big17
Да, я знаю особенности и различия char/nchar/varchar/nvarchar/max...

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

Условия которые не обойти:
- не уложится в 4000 или в 8000 символов (даже внутри каждой итерации)


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

	SET NOCOUNT ON

	DECLARE @i int = 0
	DECLARE @SqlText nvarchar(max) = ''

	DECLARE @MyTableVar table(SqlText nvarchar(max))


	WHILE (@i < 10000)
	BEGIN

		SET @i = @i + 1

		SET @SqlText = 'ffffffffffffffffffffffffffffff'

		INSERT INTO @MyTableVar (SqlText)
		VALUES (@SqlText)

	END


	 SELECT @SqlText = STUFF((SELECT '' + [SqlText]
	  FROM @MyTableVar
	   for xml path('')) ,1,1, '' )

   SELECT LEN(@SqlText)


Работает заметно быстрее, даже на значительно больших количествах строк.

таки не ответили на вопрос про языки программирования и работу с памятью )
28 мар 18, 12:43    [21292897]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
Massa52
Member

Откуда:
Сообщений: 379
Big17,
Суммирование строк ваще тяжелая операция - зависит от способа организации строковых данных.
Это перегоняется масса байтов. Каждый раз при суммировании резервируется новый участок памяти и туда пузырятся обе строки. И если это в цикле - получается кошмар в цикле.
В VB.NET и с# для ускорения/оптимизации этой операции создан спецкласс StringBuilder.
28 мар 18, 14:12    [21293210]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34608
	DECLARE @i int = 0
	DECLARE @Text nvarchar(max) = ''

	WHILE (@i < 10000)
	BEGIN
		SET @i = @i + 1
		SET @Text += 'ffffffffffffffffffffffffffffff'
	END



Это что же, сиквел при такой конкатенации вытаскивает все в память, выполняет операцию, а затем опять скидывает в кэш/на диск.

О чём ты, какой диск ? тут нет никаких операций с диском вообще.
Всё в памяти.

Т.е. не просто добавляет нужные символы к уже висящей в памяти переменной?

Именно так.

Собственно вопроса два:
1. Почему так происходит? (можете просто ссылкой кинуться в меня)
2. А есть ли альтернативные способы такой конкатенации?

1) потому.
2) нет.

У тебя просто происходит квадратичный взрыв объёма данных. 10000 * length('ffffffffffffffffffffffffffffff') (или length('ffff') ),
4 и 30.
28 мар 18, 23:39    [21294721]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Big17
В реальной задаче у меня конечно же не цикл, а курсор, который формирует итоговый набор данных.
Курсор без цикла? Ооооо. :-)

Попытайтесь переделать ваш цикл в запрос, тогда будет быстрее, может быть.
29 мар 18, 08:46    [21294915]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
alexeyvg
Попытайтесь переделать ваш цикл в запрос, тогда будет быстрее, может быть.

Ой, не заметил, вы уже нашли:
Big17
Сейчас нашел еще одно решение: вместо конкатенации вставляю значения во временную табличку, а затем из накопленных там строк формирую итоговую строчку.

Да, или так, или вообще вот это "формирую" сразу делать из исходных данных, без вставки во временную табличку.
Так сказать, работать с РСУБД именно как с РСУБД, операциями над множествами на платформе, созданной для этих операций.
29 мар 18, 08:50    [21294923]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
лолл
Member

Откуда:
Сообщений: 450
Вместо цикла рекурсивное CTE не пробовали?
2 апр 18, 12:14    [21304646]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
лолл
Вместо цикла рекурсивное CTE не пробовали?
Он уже нашёл вариант лучше.
2 апр 18, 12:45    [21304815]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5955
Massa52
Big17,
Это перегоняется масса байтов. Каждый раз при суммировании резервируется новый участок памяти и туда пузырятся обе строки. И если это в цикле - получается кошмар в цикле.
В VB.NET и с# для ускорения/оптимизации этой операции создан спецкласс StringBuilder.

Это потому что в дотнете класс string - immutable, и конкатенация порождает новый экземпляр. В прочих случаях (например, в том же MSSQL) всё может быть совсем иначе.
2 апр 18, 14:45    [21305382]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7762
Конкатенация в памяти Windows намного медленнее конкатенации на диске.
Use it wisely.
2 апр 18, 19:06    [21306394]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
Massa52
Member

Откуда:
Сообщений: 379
[quot Сон Веры Павловны]
Massa52
Big17,
Это потому что в дотнете класс string - immutable, и конкатенация порождает новый экземпляр. В прочих случаях (например, в том же MSSQL) всё может быть совсем иначе.


http://www.sqlservercentral.com/blogs/philfactor/2009/02/16/be-careful-with-string-concatenations-in-sql-server-with-big-strings/
Posted by RBarryYoung on 18 February 2009
It has long been known that naive linear string concatenation is an O(n^2) operation (more specifically, it's triangular). That simple fact is one of the big reasons that .NET has the StringBuilder class (which uses the strings->array->mass_concatenate trick that peter alludes to).

As it happens, I spent quite a lot of time last month investigating this problem and potential solutions and let me just say: it's tough in SQL. Here are the problems:

1) SQL Server apparently (based on my tests) already does the "buffer-extension" trick available to mutable strings. Unfortunately, the Extension trick does NOT solve the O(n^2) problem, it just partially alleviates it at the lower end because some percentage(k) of appends can be extensions instead of creating a new string. Effectively it changes the {(n)*(n+1)/2} cost of the naive implementation to {n +(n)*(n-1)/(2*k)} where "k" is that percentage. It's better, but it's still O(n^2).

2) The "array & mass-concatenate" trick is not available to T-SQL, not because SQL doesn't have arrays (tables serve the same purpose), but because AFAIK, there is no function that can take a variable "collection" of strings (table, array, whatever) and produce an output string.

3) The "pre-allocate and Stuff" trick popular with mutable strings is not workable in T-SQL because the STUFF() function in T_SQL is NOT like the function of the same name in some general purpose languages: the T-SQL STUFF() is an RHS (right-hand side) function and NOT an LHS (left-hand side) function. AFAIK, there is no function in SQL that can (physically) write into a pre-existing string.

That all said, I did eventually find a way to do it. Not in the O(n) time (linear) achievable in most general purpose languages, but I could get it down to O(n*Log(n)) time which is still a huge improvement. Unfortunately, I do not have it together in a presentable form and it would take me some time to do so (a day or so), but I can get it to you if you want.
3 апр 18, 03:20    [21306758]     Ответить | Цитировать Сообщить модератору
 Re: Конкатенация большого количества значений  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5955
Massa52
2) The "array & mass-concatenate" trick is not available to T-SQL, not because SQL doesn't have arrays (tables serve the same purpose), but because AFAIK, there is no function that can take a variable "collection" of strings (table, array, whatever) and produce an output string.

Дело не в отсутствии массивов - их вполне, начиная с 2008-й версии, можно заменить на UDTT, и написать свою функцию для конкатенации (другой вопрос, зачем это нужно, если посредством cross/outer apply можно прицепить конкатенацию через for xml path). Просто этот дотнетовский "array & mass-concatenate" trick - вовсе не trick: в StringBuilder в итоге конкатенация небольших объемов сводится к unsafe операциям с указателями, а на больших - вызывается managed-аналог сишного memmove (тоже, разумеется, unsafe). Т.е. сводится к прямой работе с памятью, которая, разумеется, в T-SQL недоступна.
3 апр 18, 06:57    [21306799]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить