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

5 komentarzy

Leave a Comment

Komentarze mile widziane.

Jeżeli chcesz do komentarza wstawić kod, użyj składni:
[code]
tutaj wstaw swój kod
[/code]

Jeżeli zrobisz literówkę lub zmienisz zdanie, możesz edytować komentarz po jego zatwierdzeniu.