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

Откуда:
Сообщений: 31
Доброго времени суток.

Есть запрос на обработку строк в таблице:
while @i<=(select max(id) from tmp_table)
begin
	set @sql = concat(@sql,'insert into ##tmp values (')
	select @str = col from tmp_table where id = @i
	set @chrpos = 0
	while @chrpos <> len(@str)
	begin 
		set @chrpos = CHARINDEX ('#',@str)
		set @sql = concat(@sql,'''',rtrim(left(@str,@chrpos - 1)),'''',',')
		set @str = STUFF(@str, 1, @chrpos, '')
		set @chrpos = 0;
	end 
	set @sql = concat(left(@sql,len(@sql)-1),',0)')
	exec (@sql)
	set @sql = ''
	delete from tmp_table where id = @i
	set @i = @i + 1
end


обрабатываемые строки вот такого вида:
text1#text1#text1#text1#text1#text1#text1#text1#...
#-разделитель будущих столбцов в строке
Если в строке около 20 предполагаемых столбцов и строк около 40к, то запрос такой выполняется почти 14 минут.
Не могли бы подсказать или навести на мысль - как можно ускорить данную обработку? Пробовал через GOTO (никогда до этого с ним дело в SQL не имел, но тут чего-то вспомнился) - результат тот же самый.
Заранее спасибо!
19 июн 14, 10:37    [16187430]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
_TempFox_
Member

Откуда: из норы
Сообщений: 33
Попробуйте написать следующий цикл с массовой обработкой строк:

1. Условие цикла: пока в таблице tmp_table есть записи.

2. Тело цикла:

а) в таблицу ##tmp с помощью insert ... select вставляются строки text1, выделенные из каждой записи tmp_table до первого знака #.

б) таблица tmp_table обновляется, удаляются начальные text1# из каждой строки.

в) из таблиц ##tmp и tmp_table удаляются пустые строки, where col is null or col = ''.


Посмотрите, будет ли такой цикл работать быстрее вашего.
19 июн 14, 11:04    [16187651]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
https://www.sql.ru/articles/mssql/03060701arraysandlistsinsqlserver.shtml
19 июн 14, 11:06    [16187666]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
вероятно, речь о следующем:
declare @tmp_table table (id int identity primary key, col varchar(100));
insert @tmp_table (col) values
    ('11###14')
   ,(null)
   ,('31#32#33#34#35#36#37')
   ,('41')

+
;with rec as(
   select t.id, 1 p, convert(varchar(100),left(t.col,d.d-1)) val, stuff(t.col+'#',1,d.d,'')s
      from @tmp_table t
      cross apply(select CHARINDEX('#',t.col+'#')d)d
      where d.d>0
   union all
   select t.id, p+1, convert(varchar(100),left(t.s,d.d-1)), stuff(t.s,1,d.d,'')
      from rec t
      cross apply(select CHARINDEX('#',t.s)d)d
      where d.d>0
   )
select * 
-- into #temp
from (
   select id,p,val
   from rec
   )r
pivot(max(val) for p in ([01],[02],[03],[04],[05],[06]))p
Не уверен насчет оптимальности при таком объеме данных, но должно бы сработать... 40к*20 полей=800к развернутых строк, да без индекса... ХЗ...
id010203040506
11114NULLNULL
3313233343536
441NULLNULLNULLNULLNULL
19 июн 14, 12:38    [16188390]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
SQLBeginner2014
Member

Откуда:
Сообщений: 31
Всем спасибо, придумал что-то такое:
while @i<=(select isnull(max(id1),0) from ##tmp)
begin
	select @sql = 'insert into ##fptmp values ('''
	select @str = v from ##tmp where id1 = @i
	select @str = replace(@str,'#',''',''')
	select @sql = @sql + @str
	select @sql = left(@sql,len(@sql)-1) + '0)'
	EXEC sp_executesql @sql
	select @str = ''
	select @i = @i + 1
end

##tmp - индексированная, во время теста в ней было 45к строк, надо было разбить на 20 столбца, обработка прошла за 24 сек.

Cygapb-007, спасибо, на досуге попробую ваш вариант для общего развития
19 июн 14, 16:58    [16190897]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
SQLBeginner2014,

а что будет с глобальной временной таблицей,
если несколько пользователей одновременно воспользуются Вашим скриптом?
19 июн 14, 17:19    [16191031]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
SQLBeginner2014
Member

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

это невозможно, т.к. скрипт только я буду запускать (пока - только я), но спасибо, об этом как-то не подумал, учту
19 июн 14, 17:28    [16191109]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить