Końkatenacja

https://xpil.eu/13Kt4 📋

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...

https://xpil.eu/13Kt4 📋