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

Откуда:
Сообщений: 64
Доброго времени суток, для сбора записей в строку всегда использовал конструкцию select @a = @a + [поле] from [таблица]
Недавно возникла необходимость сложить большое кол-во записей - более 500 000 - при этом общий объем инфы около 10 мб. (не много)
Запрос стал отрабатывать очень медленно. В чем возможная причина такого замедления, или может есть другой способ собрать быстро записи в строку?

Пример для тестов:
--MS SQL Server 2008 R2
--Задача: - собрать записи в одну строку
--У меня этот вариант работает хорошо до 100000 тысяч записей (менее 1сек) (120000 уже медленно, более 14 сек)
--Замедление в работе по всей видимости связано 
--с цикличным выделением памяти для переменной @str
--поскольку на снижение производительности больше влияет кол-во циклов сложения записей, 
-- а не их размер. Причем при достижении определенного размера поизводительность 
-- падает в геометрической прогрессии, при этом общий размер памяти не превышает и 3мб

declare @str varchar(max)
declare @n int
create table #t(st varchar(30))
set @n = 0
--Заполняем временную таблицу тестовыми значениями
select 1, GETDATE()--Для оценки времени выполнения
while @n <=100000
 begin
 insert #t (st) values (CONVERT(varchar(30), @n))
 set @n = @n + 1
 end
 ----------------------------------------------
 
select 2, GETDATE()--Для оценки времени выполнения
--Собираем записи в строковую переменную @str
select @str = isnull(@str,0) + ST
from #t
---------------------------------------------
select 3, GETDATE()--Для оценки времени выполнения

drop table #t

select @str
5 дек 12, 12:59    [13580620]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро собрать записи в строку?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
varchar (max) не быстрый сам по себе.
5 дек 12, 13:02    [13580650]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро собрать записи в строку?  [new]
Vicont_rtf
Member

Откуда:
Сообщений: 64
Гавриленко Сергей Алексеевич
varchar (max) не быстрый сам по себе.


Согласен, но снижение производительности выглядит не логично 130 000 тысяч более 3 минут, 200 000 уже дождаться не могу
5 дек 12, 13:06    [13580711]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро собрать записи в строку?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Гавриленко Сергей Алексеевич
varchar (max) не быстрый сам по себе.

кста ,если поменять к примеру на varchar(4000) - время исполнения миллисекунды ,для 150к записей
5 дек 12, 13:15    [13580821]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро собрать записи в строку?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Видимо, какие-то чудеса с выделением памяти.
5 дек 12, 13:27    [13580989]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро собрать записи в строку?  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
select @str = (select st from #t for xml path(''), type).value('.', 'varchar(max)')
5 дек 12, 13:28    [13580994]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро собрать записи в строку?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Vicont_rtf,

Я тоже заметил подобный эффект (ещё на SQL Server 2005). Начиная с некоторой длины строки (512 килобайт?), производительность конкатенации строк резко падает.

Вот тестовый скрипт. В цикле собирается строка порциями по 10 байт, и через каждый 10000 байт идёт замер времени конкатенации этого 10000-байтного куска:
declare @s varchar(max), @sum varchar(max), @i int, @output varchar(max), @dat datetime;
set @s = '1234567890';
set @sum = '';
set @dat = getdate();
set @i = 0;
while @i < 56000
begin
      set @sum = @sum + @s;
      set @i = @i + 1;
      if @i % 1000 = 0
      begin
            set @output = 'ms: ' + cast(datediff(millisecond, @dat, getdate()) as varchar) + '; length: ' + cast(@i * 10 as varchar);
            raiserror(@output, 0, 0) with nowait;
            set @dat = getdate();
      end;
end;


Вот кусок лога, где идёт переход через границу 512 килобайт:
ms: 180; length: 490000
ms: 183; length: 500000
ms: 186; length: 510000
ms: 190; length: 520000
ms: 2753; length: 530000
ms: 4603; length: 540000
ms: 4620; length: 550000
ms: 4633; length: 560000

Падение производительности в десятки раз. Эффект повторяется на всех доступных мне инсталляциях 2005/2008/2008R2. На версии 2012 не проверял.

Vicont_rtf,

Используйте xml, код будет выглядеть примерно так:
set @str = (select cast(st as varchar(30)) from #t for xml path(''), type).value('.', 'varchar(max)')
5 дек 12, 13:38    [13581118]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро собрать записи в строку?  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
varchar как бы намекает...
Знакомые с азами программирования должны сразу понять причину падения производительности.
5 дек 12, 13:53    [13581284]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро собрать записи в строку?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
ambarka_max
varchar как бы намекает...
Знакомые с азами программирования должны сразу понять причину падения производительности.

Объясните пожалуйста причину падения производительности при переходе через границу 512K. Одним белым пятном в знании SQL Server у меня станет меньше.
5 дек 12, 13:56    [13581337]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро собрать записи в строку?  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
Да причем тут граница. varchar всему виной. Ну возможно MS заложил минимальный буфер под varchar(max) в 512 килобайт, из-за того и "быстро", хотя это все равно ужасно медленно по сравнению с char(8000). Сделате тип данных char(8000) и посмотрите чем выделение памяти сразу выгодно отличается от перераспределения на каждой итерации цикла. Сразу же от varchar убежите :) Нужно придумать как сразу выделить память. Не знаю точно как, либо использовать много раз char(8000) либо сразу выделить varchar(max) переменной нужный кусок памяти засунув туда что-то такое... большое.
5 дек 12, 14:03    [13581409]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро собрать записи в строку?  [new]
Vicont_rtf
Member

Откуда:
Сообщений: 64
Гость333 , СПАСИБО!
5 дек 12, 14:04    [13581424]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро собрать записи в строку?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
ambarka_max
Нужно придумать как сразу выделить память. Не знаю точно как, либо использовать много раз char(8000) либо сразу выделить varchar(max) переменной нужный кусок памяти засунув туда что-то такое... большое.
Можно попробовать выделить память в varchar(max), а потом вместо сложения строк заменять куски функцией STUFF
5 дек 12, 15:28    [13582124]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро собрать записи в строку?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
alexeyvg
ambarka_max
Нужно придумать как сразу выделить память. Не знаю точно как, либо использовать много раз char(8000) либо сразу выделить varchar(max) переменной нужный кусок памяти засунув туда что-то такое... большое.
Можно попробовать выделить память в varchar(max), а потом вместо сложения строк заменять куски функцией STUFF

Это как?
set @str = stuff(@str, <start>, <length>, <character_expression>)

?
Как мне кажется, будет создаваться новый экземпляр строки для переменной @str и заполняться результатом работы функции stuff. Память, выделенная под старый экземпляр строки, будет освобождаться. Хотя я и не уверен в этом.
5 дек 12, 15:39    [13582206]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро собрать записи в строку?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
Гость333
?
Как мне кажется, будет создаваться новый экземпляр строки для переменной @str и заполняться результатом работы функции stuff. Память, выделенная под старый экземпляр строки, будет освобождаться. Хотя я и не уверен в этом.
Да, может и так, нужно проверять.
5 дек 12, 15:54    [13582330]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить