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

Откуда:
Сообщений: 2696
Доброго дня господа !

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

Исходный поток
F1 F2 F3
1 2 3
1 4 3
2 4 3
1 2 3

Выходной поток
F1 F2 F3
1,2 2,4 3

Знаю прием с "for xml path(''), но мало подходит так как необходимо многоразово обращаться
к данному обьекту

SELECT (select distinct F1+',' ............. for xml path),
(select distinct F2+',' ............. for xml path),
(select distinct F3+',' ............. for xml path),
............................................................
............................................................
(select distinct FN+',' ............. for xml path),

Большие потери времени при отработке запроса (буферный кэш помогает слабо)
Может есть еще прием, подскажите .......


Спасибо
16 авг 12, 11:23    [13019194]     Ответить | Цитировать Сообщить модератору
 Re: Конкатинация уникальных значений строки  [new]
qwerty112
Guest
HOME_X,

сделай unpivot с distinct во времянку, и for xml path - делай уже из времянки,
избавишся от этих 100500 distinct - имхо, будет быстрее
16 авг 12, 12:33    [13019669]     Ответить | Цитировать Сообщить модератору
 Re: Конкатинация уникальных значений строки  [new]
qwerty112
Guest
+ времянке индекс на {F,val}
16 авг 12, 12:35    [13019681]     Ответить | Цитировать Сообщить модератору
 Re: Конкатинация уникальных значений строки  [new]
user89
Member

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

на основе того, что предложил qwerty112 (Добрый Э-Эх ???)

declare @t table (F1 varchar(50), F2 varchar(50), F3 varchar(50))
insert @t select '1','2','3' union all select '1','4','3' union all select '2','4','3' union all select '1','2','3' union all select '8','4','3'
select * from @t

if object_id('tempdb..#t') is not null drop table #t

select distinct i.val, i.f
into #t
from @t
cross apply (select f1, 'F1' union all select f2, 'F2' union all select f3,'F3') i(val,f)

create clustered index f_idx on #t(f)

select substring((select ', ' + val from #t where f = 'F1' for xml path('')), 3, 2000000000) [F1],
substring((select ', ' + val from #t where f = 'F2' for xml path('')), 3, 2000000000) [F2],
substring((select ', ' + val from #t where f = 'F3' for xml path('')), 3, 2000000000) [F3]
17 авг 12, 14:55    [13027055]     Ответить | Цитировать Сообщить модератору
 Re: Конкатинация уникальных значений строки  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
user89
substring(..., 3, 2000000000)
Человек не знает что такое Stuff, и его совершенно не смущает костылявость своей конструкции (а она вообще работает?) и не как не провоцирует хотябы пробежаться по списку функций. Не?
И вообще, коль начали писать пишите уже нормальное всё:
Stuff(SELECT ',', ... FOR XML Path(''),Type).value('text()[1]','VarChar(max)'),1,1)

Индекс надо сразу иметь (т.е. таблицу заранее), без бесполезного перелопачивания данных.

HOME_X, откуда вы такие задачи берёте? С потолка?
И зачем вам скорость, всё равно работа одноразовая?

HOME_X
Знаю прием с "for xml path(''), но мало подходит так как необходимо многоразово обращаться к данному обьекту
1. Многоразово не значит не эффективно. Если результатов в итоге очень много, то ровно наоборот.
2. FOR XML тут не причём дело в множественной уникальности (DISTINCT)
18 авг 12, 01:05    [13029668]     Ответить | Цитировать Сообщить модератору
 Re: Конкатинация уникальных значений строки  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Ещё можно попробывать columnstore индекс.
Вроде прям как раз под задачу.
18 авг 12, 01:18    [13029692]     Ответить | Цитировать Сообщить модератору
 Re: Конкатинация уникальных значений строки  [new]
user89
Member

Откуда:
Сообщений: 2083
Mnior
Человек не знает что такое Stuff, и его совершенно не смущает костылявость своей конструкции (а она вообще работает?) и не как не провоцирует хотябы пробежаться по списку функций. Не?

Я хорошо знаю строковые функции. На больших данных substring работает чуть быстрее.
Вот тесты на таблице с 1 млн. записей.
+ substring. CPU time = 234 ms, elapsed time = 1253 ms.
set nocount on

if object_id('tempdb..#t') is not null drop table #t
if object_id('tempdb..#main') is not null drop table #main

create table #main (F1 varchar(50), F2 varchar(50), F3 varchar(50))

declare @i int = 0
while @i < 500000
begin
  insert #main select left(abs(checksum(newid())), 5), left(abs(checksum(newid())), 5), left(abs(checksum(newid())), 5)
  union all select left(abs(checksum(newid())), 5), left(abs(checksum(newid())), 5), left(abs(checksum(newid())), 5)
  set @i = @i + 1
end

select distinct i.val, i.f
into #t
from #main
cross apply (select f1, 'F1' union all select f2, 'F2' union all select f3,'F3') i(val,f)

create clustered index f_idx on #t(f)

set statistics time on

select substring((select ',' + val from #t where f = 'F1' for xml path(''),type).value('text()[1]','NVarChar(max)'), 2, 2147483647) [F1],
substring((select ',' + val from #t where f = 'F2' for xml path(''),type).value('text()[1]','NVarChar(max)'), 2, 2147483647) [F2],
substring((select ',' + val from #t where f = 'F3' for xml path(''),type).value('text()[1]','NVarChar(max)'), 2, 2147483647) [F3]

set statistics time off

if object_id('tempdb..#t') is not null drop table #t
if object_id('tempdb..#main') is not null drop table #main
+ stuff. CPU time = 245 ms, elapsed time = 1461 ms.
set nocount on

if object_id('tempdb..#t') is not null drop table #t
if object_id('tempdb..#main') is not null drop table #main

create table #main (F1 varchar(50), F2 varchar(50), F3 varchar(50))

declare @i int = 0
while @i < 500000
begin
  insert #main select left(abs(checksum(newid())), 5), left(abs(checksum(newid())), 5), left(abs(checksum(newid())), 5)
  union all select left(abs(checksum(newid())), 5), left(abs(checksum(newid())), 5), left(abs(checksum(newid())), 5)
  set @i = @i + 1
end

select distinct i.val, i.f
into #t
from #main
cross apply (select f1, 'F1' union all select f2, 'F2' union all select f3,'F3') i(val,f)

create clustered index f_idx on #t(f)

set statistics time on

select stuff((select ',' + val from #t where f = 'F1' for xml path(''),type).value('text()[1]','NVarChar(max)'), 1, 1, '') [F1],
stuff((select ',' + val from #t where f = 'F2' for xml path(''),type).value('text()[1]','NVarChar(max)'), 1, 1, '') [F2],
stuff((select ',' + val from #t where f = 'F3' for xml path(''),type).value('text()[1]','NVarChar(max)'), 1, 1, '') [F3]

set statistics time off

if object_id('tempdb..#t') is not null drop table #t
if object_id('tempdb..#main') is not null drop table #main

Гонял несколько раз. На 1 млн. записей substring выигрывал у stuff где-то 200-250 ms.
18 авг 12, 14:04    [13030621]     Ответить | Цитировать Сообщить модератору
 Re: Конкатинация уникальных значений строки  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
user89
Гонял несколько раз. На 1 млн. записей substring выигрывал у stuff где-то 200-250 ms.
Интересно.
Если оно так, звиняюсь, беру слова обратно.

Только не понял как вы тестировали, на 3х вызовах?! (они же после агрегирования вызываются) Чтоб такая разница, как-то не очень верится.
Давайте лучше чистый скрипт на огромных количествах строк, и с разными длинами.
Возможно уже были тесты представлены на форуме, тогда кидайте ссылку.
20 авг 12, 02:30    [13034820]     Ответить | Цитировать Сообщить модератору
 Re: Конкатинация уникальных значений строки  [new]
user89
Member

Откуда:
Сообщений: 2083
Mnior,
тестировал так.
set nocount on

/********** Тестируем substring **********/
Create table #t (Account varchar(500))

declare @i int = 1

-- 2 млн. записей
while @i < 2000001
begin
  insert #t
    select 'Account ' + cast(@i as varchar(7))  
    
  set @i = @i + 1    
end

-- Отвлечем сервер от таблицы #t
select top 2000 * from sys.objects a1, sys.objects a2
select top 2000 * from sys.columns a1, sys.columns a2

set statistics time on
select substring((select ',' + Account from #t for xml path(''),type).value('text()[1]','NVarChar(max)'), 2, 2147483647)
set statistics time off

if object_id('tempdb..#t') is not null drop table #t




/********** Тестируем stuff. В другом окне Management Studio **********/
Create table #t (Account varchar(500))

declare @i int = 1

-- 2 млн. записей
while @i < 2000001
begin
  insert #t
    select 'Account ' + cast(@i as varchar(7))  
    
  set @i = @i + 1    
end

-- Отвлечем сервер от таблицы #t
select top 2000 * from sys.objects a1, sys.objects a2
select top 2000 * from sys.columns a1, sys.columns a2

set statistics time on
select stuff((select ',' + Account from #t for xml path(''),type).value('text()[1]','NVarChar(max)'), 1, 1, '')
set statistics time off

if object_id('tempdb..#t') is not null drop table #t


После нескольких запусков, цифры примерно одинаковые, крутятся возле этих значений:
Для substring
 SQL Server Execution Times:
   CPU time = 4875 ms,  elapsed time = 5132 ms.

Для stuff
 SQL Server Execution Times:
   CPU time = 4828 ms,  elapsed time = 5378 ms.


Один раз выдал 10 сек. на substring, 19 на stuff, но это случайность.
В целом, stuff проигрывает немного, и выбор между substring или stuff - дело вкуса...
20 авг 12, 13:57    [13036887]     Ответить | Цитировать Сообщить модератору
 Re: Конкатинация уникальных значений строки  [new]
Валдай
Member

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

чуть меньше копипаста

select *
from (
  select f,list = substring((select ', ' + val from #t where f = F.f for xml path('')),3, 2000000000) 
  from ( select distinct f from #t ) F
)p pivot ( max(list) for f in ([F1],[F2],[F3]) )s
20 авг 12, 15:52    [13037808]     Ответить | Цитировать Сообщить модератору
 Re: Конкатинация уникальных значений строки  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
user89, не знаю зачем вы так извращённо меряли один запуск на большой строке.
Вот попроще:
SET NOCOUNT ON
DECLARE	 @BigVar1 NVarChar(max) = Replicate(Convert(NVarChar(max),'##########'),1000000)
	,@BigVar2 NVarChar(max)
SET STATISTICS TIME ON
-- DBCC FREESYSTEMCACHE ('ALL')
SELECT @BigVar2 = Stuff    (@BigVar1,1,1,'')
SELECT @BigVar2 = SubString(@BigVar1,2,2147483647)
SET STATISTICS TIME OFF
--   Время ЦП = 124 мс, затраченное время = 123 мс.
--   Время ЦП = 125 мс, затраченное время = 116 мс.
20 авг 12, 18:25    [13038813]     Ответить | Цитировать Сообщить модератору
 Re: Конкатинация уникальных значений строки  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Ах да, видно что зависит от размера строки, что мной воспринимается как не эффективность в целом.
20 авг 12, 19:13    [13039098]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить