Teksta savirknēšana

Jau divos raktos pieminēta teksta savirknēšanas vajadzība. Šeit 4 dažādi risinājumi vēlamā rezultāta panākšanai.

Tas ko vēlos panākt, ir pēc iespējas efektīvāks veids, kā panākt teksta virknes izveidi. Nepieciešamais rezultāts ir šāds:

Iespējamie risinājumi:

1. Funkcija, kuras iekšienē ir kursors
2. Funkcija, kuras iekšienē ir izmantots "viltotais kursors" (plašāk SQL Server kursors bez kursora)
3. CLR agregātfunkcija (izmantota: CLR agregātfunkcijas piemērs)
4. FOR XML (ideja no raksta)

Jau raksta sākumā rezultāti, kas iegūti salīdzinot ātrdarbību uz 100 000 galvas tabulu rindu un 450 609 detaļas tabulu rindu. Jāpiebilst, ka rezultāti nepretendē uz absolūti objektīvu rādītāju, bet priekšstatu dod (to varētu nosaukt par TTT principu- vajag testēt, testēt un vēlreiz testēt).

rezultāti izskatās tā (pirmie bez indeksa, otrie ar indeksu uz detaļu tabulas kolonnas TabGalvaID):

Rezultāti "noķerti" ar Profiler. Godīgi sakot mazliet pārsteidz CLR agregātfunkcijas pārrākums Read operāciju ziņā, tomēr nevaru atrast nevienu iemeslu piesieties skaitļiem- visi trīs pārējie risinājumi katru reizi meklē detaļu tabulā atbilstošās rindas. Savukārt CLR gadījumā gan galvas gan detaļu tabula tiek nolasīta tikai vienu reizi!
[
Neizturēju un uztaisīju vēl aprēķinu- cik lapu teorētiski jānolasa gadījumā, ja detaļu tabulai ir indeks uz TabGalvaID. Aprēķinā izmantots tas, ko var izlasīt rakstos Indeksi (non-clustered indexes) un Informācijas par indeksiem apskatīšana.
Sanāk, katrai galvas rindai, detaļu tabulā tiks pārmeklēts neklasterētais indekss, tātad:
100 000 * 3 (3 ir neklasterētā indeksa dziļums- tātad, lai atrastu indeksa datu lapas vajag nolasīt 3 lapas). Vidēji vienai atrastajai vērtībai indeksā būs 4.5 vērtības (1 galvas tabulas ierakstam vidēji 4.5 detaļu rindas), lai līdz klasterētā indeksa datu lapai nonāktu, jāpārmeklē klasterētais detaļu tabulas indekss, kura dziļums arī ir 3, tātad- 4.5 * 3 * 100 000. aprēķinā galvas tabulu maz maina, jo tā ir relatīvi maza- 445 lapas.
Kopā sanāk:
3 * 100 000 + 4.5 * 3 * 100 000 + 445 = 1 650 445
Tas savukārt sanāk ļoti, ļoti tuvu tam, kā strādā "viltotais kursors".
Jautājums uz kuru vēl būtu jāatbild- kāpēc īstajam kursoram vajadzēja vēl 2x vairāk kā šis teorētiskais aprēķins.
]

Un kā redzams, CLR konkrētajā gadījumā sanāk labākais risinājums.
Starp citu- minētajā rakstā arī ātrdarbības salīdzinājumi, tomēr tur
* nav aprakstīta CLR agregātfunkcija
* nav galvas-detaļu tabulas, kas maina ideju
(tāpēc pamēģināju pats).


Testa tabulu sagatavošana
Tiesa gan, 100 000 rindu galvas tabulā ir mazliet par daudz:

create table dbo.TabGalva
(
    TabGalvaID int primary key identity,
    Teksts nvarchar(30)
)
Go
create table dbo.TabDetalas
(
    TabDetalasID int primary key identity,
    TabGalvaID int,
    Saisinajums nvarchar(5)
)
Go
Set NoCount On
Declare @arejais int
Declare @id int
Declare @rSkaits int
Set @Arejais = 0;
While @arejais < 100000 -- 100 000 galvas rindiņu un X detaļu rindiņu
    Begin
        Insert Into dbo.TabGalva(Teksts)
            Values (Replicate('x', Cast(Rand()*20 as int)))
        Set @id = @@Identity
        Set @rSkaits = Cast(Rand()*10 as int)
        While @rSkaits > 0
            Begin
                Insert Into dbo.TabDetalas(TabGalvaID, Saisinajums)
                    Values (@id, Replicate('Y', Cast(Rand()*5 as int)))
                Set @rSkaits = @rSkaits - 1
            End
        Set @Arejais = @Arejais + 1;
    End
Go 

Kursors
Kursors tiek realizēts ar funkcijas palīdzību:

Create Function fn_VirkneTekstu_Kursors
(
    @TabGalvaID int
)
Returns NVarChar(1000)
As
Begin
    DECLARE @n NVARCHAR(5)
    DECLARE @s NVARCHAR(1000)
    DECLARE c CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    SELECT Saisinajums FROM dbo.TabDetalas Where TabGalvaID = @TabGalvaID
       
    OPEN c;       
    FETCH NEXT FROM c INTO @n;       
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @s = COALESCE(@s + ';' + @n, @n);           
        FETCH NEXT FROM c INTO @n;       
    END
    CLOSE c;
    DEALLOCATE c;
    Return @s
End

Vaicājums datu izgūšanai:

Select g.TabGalvaID, dbo.fn_VirkneTekstu_Kursors(TabGalvaID)
From dbo.TabGalva g

"Viltotais kursors"
Arī tiek izmantota funkcija (sanāk īsāk nekā ar kursoru!):

Create Function fn_VirkneTekstu_ViltotsKursors
(
    @TabGalvaID int
)
Returns NVarChar(1000)
As
Begin
    Declare @txt NVarChar(1000)
      
    SELECT @txt = COALESCE(@txt + ';' + Saisinajums, Saisinajums)
    FROM dbo.TabDetalas Where TabGalvaID = @TabGalvaID
    Return @txt
End

Vaicājums datu izgūšanai:

Select g.TabGalvaID, dbo.fn_VirkneTekstu_ViltotsKursors(TabGalvaID)
From dbo.TabGalva g

CLR
Lai varētu izmantot funkciju, jāseko norādījumiem rakstā CLR agregātfunkcijas piemērs.
Vaicājums datu izgūšanai:

Select g.TabGalvaID, dbo.Concatenate(Saisinajums)
From dbo.TabGalva g
    Left Join dbo.TabDetalas d on g.TabGalvaID = d.TabGalvaID
Group By g.TabGalvaID, Teksts

FOR XML
Pie "FOR XML" vēl noteikti atgriezīšos, bet nu šeit bez garākiem paskaidrojumiem vaicājums (ideja no raksta), kurā tiek izmantota arī With klauza (CTE):

With X
as
(
    Select TabGalvaID, Saisinajums
    From dbo.TabDetalas
)
Select TabGalvaID,
    (
        SELECT Saisinajums + ';'
        FROM X i
        Where i.TabGalvaID = o.TabGalvaID
        FOR XML PATH(''), TYPE
    ).value('.[1]', 'NVARCHAR(1000)')
From X o
Group By TabGalvaID