Końkatenacja

In Branżowe by xpil5 Comments

Tym razem znów coś z zawodowego podwórka. I jak zwykle niczego nowego nie odkryję, ale może kogoś zaciekawię…

Odwieczny problem wszystkich bazodanowców: jak w sprawny, elegancki oraz wydajny sposób „spoziomić” dane; innymi słowy, przekształcić dane z postaci „pionowej” (każda wartość w osobnym rekordzie) do postaci „poziomej” (pojedyncza wartość zbudowana z wartości z poszczególnych rekordów rozdzielonych przecinkiem, średnikiem czy czym tam kto chce).

Do dalszych rozważań przyda sie skrypt tworzący tabelę testową i wypełniający ją losowymi danymi:

Tworzymy tabelę:

CREATE TABLE tbl2 (
id INT IDENTITY(1, 1)
, nm VARCHAR(100)
, snm VARCHAR(100)
)

 

Wypełniamy tabelę losowymi danymi:

DECLARE @counter INT = 0
WHILE @counter <= 1000 BEGIN
INSERT INTO dbo.tbl2 ( nm , snm )
SELECT CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25))
, CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25))
SELECT @counter+= 1
END

 

Na koniec jeszcze zakładamy indeks:

CREATE NONCLUSTERED INDEX idx01tbl2 ON tbl2 ( nm)

 

Jeszcze raz, żeby było wiadomo co próbujemy osiągnąć:

Chcemy przejść z takiej postaci:

DTOPJ
LPQYJ
YXNJQ
KBWDO
AOWEC
BHLOP

 

do takiej:

DTOPJ;LPQYJ;YXNJQ;KBWDO;AOWEC;BHLOP;

Proste? No, w zasadzie tak. Klasyczne podejście wygląda, z grubsza rzecz ujmując, o tak:

DECLARE c CURSOR FAST_FORWARD FOR
SELECT nm FROM dbo.tbl2 t
DECLARE @output VARCHAR(MAX), @cnm VARCHAR(100)
SELECT @output = ”, @cnm = ”
OPEN c
FETCH NEXT FROM c INTO @cnm
WHILE @@FETCH_STATUS=0 BEGIN
SELECT @output += (@cnm + ‚;’)
FETCH NEXT FROM c INTO @cnm
END
CLOSE c
DEALLOCATE c
SELECT @output AS c1

 

W zasadzie dość proste, względnie czytelne („otwórz kursor, przewal wszystkie rekordy, zamnknj kursor”) i nawet jako tako działa. Dla tysiąca rekordów wykonuje się w około sekundę, dla stu tysięcy – ciut ponad 3 minuty.

A teraz coś, na widok czego starzy wyjadacze ziewną (o ile jeszcze nie zasnęli do tej pory), za to młodzi adepci SQL westchną z podziwem:

SELECT (SELECT nm + ‚;’ FROM tbl2 FOR XML PATH(”)) AS c1

 

Co to robi? Ano, to samo… Z tą tylko różnicą, że zamiast zużywać prawie 15 linii kodu, robi to samo w jednej.

A wydajność? Dla 1000 rekordów poniżej 15 milisekund, dla 100,000 rekordów 62 milisekundy. Dla pół miliona rekordów 406 milisekund (czyli nadal poniżej pół sekundy).

Jakieś wady?

Tak, jedna: specyfikacja FOR XML PATH jest słabo udokumentowana i może się w każdej chwili zmienić. Póki co jednak – działa pięknie (zarówno w 2005 jak i w 2008) – a ponieważ powyższy trick jest bardzo popularny wśród braci SQL-owej, nie sądzę, żeby Microsoft strzelił sobie samobója i wprowadził tu jakieś znaczące zmiany.

Na koniec żart. Z brodą, moje żarty się nie golą:

– Po której stronie gęś ma najwięcej piór?
– ?!?
– Po zewnętrznej…

Dodaj komentarz

5 komentarzy do "Końkatenacja"

Powiadom o
avatar
Sortuj wg:   najnowszy | najstarszy | oceniany
butter
Gość

declare @txt varchar(100)
select @txt =''
select @txt = @txt+kolumna+','
from tabela

xpil
Gość

Tak, spryciarzu – a co jeżeli chcesz pogrupować? Kursor zabangla, FOR XML PATH też.

butter
Gość

robię ładne cte ;P

B
Gość

Rewelacja! Na pewno się przyda 🙂

wpDiscuz