Jan 13 2009

Parametrul de tip tabela (Table Valued Parameter)

Categorie: Metodologie | SQL Server | T-SQL | SQL Server | T-SQLCatalin Dumitru @ 09:27

Este un nou tip de parametru in SQL Server 2008 si permite trimiterea de randuri multiple catre o instructiune T-SQL sau folosirea ca parametru la apelarea unei functii sau proceduri stocate.
 
Crearea si utilizarea TVP
Pentru a fi creat este necesar sa se defineasca numele si structura: CREATE TYPE Nume AS TABLE (coloana tip). Pentru a fi utilizat se defineste o procedura sau functie cu unul sau mai multi parametrii de tipul nou creat. Accesarea datelor se realizeaza cu ajutorul instructiunilor DML (select, insert, update, delete).
 
Avantaje:
1.       Accepta PRIMARY KEY, UNIQUE si CHECK CONSTRAINTS
2.      Se reduce numarul de apeluri catre server
3.      Se foloseste READONLY ceea ce permite transmiterea unei referinte catre rutina (astfel nu se duplica datele irosind timp si spatiu de stocare)
4.      Nu se face LOCK pentru popularea cu date
5.      Aplicatia client poate preciza ordinea de sortare si cheile unice
6.      Reprezinta un model simplu de programare
7.      Se pot programa reguli de business complexe in rutine simple
8.       Sunt strongly typed
9.       Pot exista de cardinalitati diferite (adica cu numar diferit de coloane)
10.    Beneficiaza de avantajul temporary table caching
 
Restrictii:
1.      Nu accepta DEFAULT CONSTRAINT sau crearea directa de indecsi
2.      Nu se poate altera structura TVP
3.      Declararea parametrului se face precizand READONLY ceea ce va avea ca efect restrictia de a nu altera datele (nu se pot executa instructiuni DML cu exceptia SELECT)
4.      SQL Server nu creaza si nu mentine statistici pentru coloanele unui parametru de tip tabela
5.      Nu se poate folosi ca target pentru SELECT INTO
 
Securitate:
TVP se supune regulilor de securitate ale obiectelor: CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION, si REVOKE.
 
Catalog:
1.      sys.table_types
2.      sys.parameters
3.      sys.types
 
Observatii:
1.       In spatele oricarui TVP se afla o tabela temporara stocata in Tempdb
2.       Disponibil in Framework 3.5
 
Recomandare Microsoft:
1.      A se folosi pentru mai putin de 1000 de randuri
2.      Pentru mai mult de 1000 de randuri a se folosi BULK_INSERT
 
Testare:
A fost creata o aplicatie client dezvoltata in C# avand doar o forma, 2 butoane, un textbox si un listbox. Unul dintre butoane apela o procedura si ii pasa un parametru de tip tabela iar cel de-al 2-lea buton crea o tabela temporara, adauga date in ea si apoi executa o procedura. Rolul textbox-ului a fost acela de a-mi permite introducerea unui numar intreg care sa reprezinte numarul de randuri generate pentru a testa executarea celor doua proceduri. Testarea a avut ce efect compararea timpilor de la apasarea butoanelor si pana la terminarea executiilor celor doua proceduri.
Rezultatele au fost in favoarea TVP insa timpul castigat la rulare nu a fost foarte mare, diferenta este infima insa daca s-ar folosi tabele cu un numar mare de coloane si un numar mai mare de randuri diferenta ar fi mai semnificativa. La o mie de inregistrari adaugate intr-o tabela prin cele doua metode, rezultatele au fost:
Metoda tabelei temporare: 0.156 secunde
Metoda TVP: 0.093
Timpii au diferit putin la fiecare rulare insa diferenta s-a pastrat.
 
Concluzie:
Utilizarea TVP ar aduce un avantaj prin:
1.      Timpi mai mici de executie
2.      Claritatea codului

Etichete: , ,