OLTP (online transaction preocessing) vs. BI (business intelligence)

De la inceput, sistemele relationale de baze de date au fost utilizate pentru a stoca informatii primare pentru afacere precum comenzile sau facturile utilizand procesarea bazata pe tranzactii. Aceasta orientare pe datele afacerii are avantaje si dezavantaje. Un avantaj este acela ca performanta scazuta a primelor sisteme relationale de baze de date s-a imbunatatit substantial astfel incat, in zilele noastre, multe sisteme relationale de baze de date pot executa zeci de tranzactii pe secunda (bineinteles daca si componentele hardware permit acest lucru). Pe de alta parte, orientarea spre afaceri a sistemelor tranzactionale au prevenit aparitia altor sisteme naturale de baze de date pentru a se analiza si a umple nevoia de informatii din datele existente intro companie sau department.

OLTP (online transaction processing)

Dupa cum am spus deja, performanta este principala problema a sistemelor bazate pe procesarea tranzactionala. Un exemplu tipic al aplicarii acestor sisteme este retragerea de fonduri de la un bancomat (ATM). Catva dintre principalele proprietati ale sistemelor OLTP sunt:

1.       Tranzactii scurte

2.       Multi utilizatori (sute sau chiar mii)

3.       Operatii continue de citire si scriere bazate pe un numar redus de randuri

4.       Date de dimensiuni medii sunt stocate in baza de date

Performanta bazelor de date va creste daca tranzactiile sunt scurte. Motivul este acela ca tranzactiile folosesc lock-uri pentru a preveni posibilul efect negativ  al problemelor de acces concurential. Daca tranzactiile sunt lungi, numarul de lock-uri si durata lor cresc, scazand performanta accesului la date si a performantei pentru alte tranzactii.

Sistemele OLTP mari au ,de obicei, multi utilizatori care acceseaza sistemul simultan. Un exemplu tipic este cel al unui sistem de rezervare a biletelor pentru o companie aeriana, care trebuie sa proceseze zeci de cereri ,aproape imediat, pentru bilete de calatorie intro tara sau pe intreg cuprinsul planetei. In acest tip de sistem, utilizatorii isi doresc ca asteptarile lor legate de timp sa fie satisfacute – sistemul sa raspunda cat mai repede si sa fie disponibil 24 de ore pe zi timp de 7 zile pe saptamana.

Utilizatorii sitemelor OLTP executa instructiuni DML (data manipulation language) in mod continu realizand operatii de citire si scriere in acelasi timp. Pentru ca datele unui asemenea sistem se schimba des se poate spune ca sistemul este foarte dinamic. In mod normal, aceste operatii (sau rezultatul lor) implica o cantitate mica de date, altfel, daca sistemul aceseaza multe randuri va fi nevoie sa fie accesate una sau mai multe tabele din baza de date (ceea ce poate produce lock-uri conducand spre scaderea performantelor).

In utlimii ani, cantitatea de date stocate intro baza de date operationala (baza de date gestionata de un sistem OLTP) a crescut rapid. Astazi, multe baze de date pot stoca pana la cateva zeci sau chiar sute de GB de date. Si dupa cum vom vedea, aceasta cantitate este mica in comparatie cu depozitele de date (data warehouse).

Sistemele de Inteligenta Artificiala (business inteligence)

Inteligenta artificiala (BI – business inteligence) este procesul de integrare a cantitatilor uriase de date intrun singur spatiu de stocare in care utilizatorii sa poata rula interogari si rapoarte pentru a analiza datele existente. Cu alte cuvinte, scopul BI este de a pastra datele care pot fi accesate de utilizatorii care iau decizii pe baza analizelor. Aceste sisteme sunt adesea numite analitice sau informative, pentru ca accesand datele, utilizatorii pot obtine informatii pe baza carora pot lua decizii mai bune.

Scopul sistemelor BI este diferit de scopul sistemelor OLTP. Spre exemplu urmatoarea intrebare este o interogare pentru sistemele  BI: „Care este cea mai bine vanduta categorie de produse pentru fiecare regiune in semestrul al 3-lea din anul 2008 ?”. De aceea, un sistem BI are proprietati diferite fata de cele listate la sistemele OLTP (prezentate mai sus). Cateva dintre cele mai importante proprietati ale sistemelor BI sunt urmatoarele:

1.       Operatiunile de incarcare (load) se bazeaza pe un numar mare de randuri

2.       Numar mic de utilizatori

3.       Cantitati mari de date sunt stocate in baza de date

In afara de incarcarea cu date realizata la intervale regulate (de obicei, zilnic), sistemele BI sunt de obicei, sisteme deschise doar pentru citire (de aceea natura natura datelor intrun asemenea sistem este statica). Datele sunt aduse din surse diferite, curatate (se asigura consistenta) si incarcate intro baza de date numita depozit de date (data warehouse sau data mart). Datele curatate raman, de obicei, nemodificate dar ele pot fi modificate dupa procesul de curatare si inainte de incarcare. Pentru ca sistemele BI sunt utilizate pentru a obtine informatii, numarul utilizatorilor simultani este foarte mic in comparatie cu numarul utilizatorilor care acceseaza simultan un sistem OLTP. De obicei, utilizatorii unui sistem BI genereaza rapoarte care afiseaza diferiti indicatori cu privire la starea financiara a unei intreprinderi (sau departament), sau executa interogari complexe pentru a compara datele.

O alta deosebire intre sistemele OLAP si BI este aceea legata de disponibilitate, in timp ce un sistem OLTP poate fi accesat non – stop, un sistem BI poate fi accesat numai dupa ce acesta este incarcat cu date.

In timp ce un sistem OLTP stocheaza numai datele curente, un sistem BI trebuie sa tina cont si de modificarile istorice (spuneam mai devreme ca un sistem BI realizeaza comparatii intre date, ei bine acest lucru se poate realiza pe perioade diferite de timp). Din acest motiv, cantitatea de date stocata intr-un depozit de date este foarte mare.

Parametrul de tip tabela (Table Valued Parameter)

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

Interogările recursive folosind Common Table Expressions (CTE)

Marele avantaj al introgărilor recursive este acela că se pot referii  singure. Care este rostul acestora? Pentru a reprezenta date ierarhice aşa cum este o organigramă a departamentelor dintro companie sau un meniu pentru un site web etc.
Pentru a se rezolva acest aspect au apărut mai mulţi algoritmi pe care nu o să-i discut aici însă utilizând motoarele de căutare puteţi găsi singuri foarte mulţi algoritmi. Prin interogările recursive avem posibilitatea de a standardiza modul de lucru cu datele ierarhice şi cu interogări asupra acestora.
Sintaxa:
WITH nume_CTE (optional lista_coloane) AS
 (
   Membru_ancora
   UNION ALL
   Membru_recursiv
 )
INTEROGARE ASUPRA nume_CTE
OPTION (MAXRECURSION n)
 
Se poate observa că interogarea recursivă este formată din două interogări numite membru ancoră şi  membru recursiv. Membrul ancoră este interogarea al cărei rezultat  va fi folosit ca intrare pentru membrul recursiv. Aceasta se va autoapela până când niciun rând nu mai este întors (condiţie de încheiere). Ar mai fi de menţionat ca reguli pentru interogările recursive că “UNION ALL” este singura construcţie permisă între cei doi membrii şi că membrul recursiv poate referii doar o singura interogare recursivă (CTE).
Un exemplu folosit pentru a înţelege recursivitatea este calculul factorialului. Spre exemplificare se poate folosi urmatorul script:
CREATE FUNCTION dbo.Factorial(@Numar int)
RETURNS int AS BEGIN
 
      IF @Numar = 0 BEGIN
            RETURN 1
      END
 
      RETURN @Numar * dbo.Factorial(@Numar – 1)
END
 
Şi testarea:
SELECT  dbo.Factorial(1) AS [1!],          
                  dbo.Factorial(2) AS [2!],
                  dbo.Factorial(3) AS [3!],
                  dbo.Factorial(4) AS [4!],
                  dbo.Factorial(5) AS [5!]
Se poate observa că funcţia se autoapeleaza până când @Numar = 0 (pentru cine nu ştie: n! = 1*2*..(n-1)*n)
Să analizăm comparativ exemplul de mai sus cu CTE. Avem un parametru de intrare iar în CTE avem membru de intrare. Apoi după condiţia de continuitate se autoapelează funcţia cam în acelaşi mod în care membrul recursiv se autoapelează decrementând valoarea parametrului (în CTE se preia valoarea părintelui astfel se avanseaza cu un nivel). Când parametrul funcţiei va avea valoarea 0, se opreşte autoapelarea, iar în CTE ne oprim când nu mai avem părinte, am ajuns la condiţia de ieşire. Când s-a ajuns la nivelul maxim de iterare, valorile din stivă se vor asigna variabilei rezultat, se va combina cu membrul ancora iar rezultatul este afisat.
Exemplu:
CREATE TABLE Arbore (
      ID_Nod                    int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
      ID_NodParinte     int NULL
            CONSTRAINT [FK_Arbire_ID_Nod] FOREIGN KEY REFERENCES dbo.Arbore(ID_Nod),
      Informatie              nvarchar(200) NOT NULL,
            CONSTRAINT  [CK_ID_NodParinte] CHECK (ID_Nod <> ID_NodParinte)
)
 
GO
INSERT INTO Arbore (ID_NodParinte, Informatie)
VALUES (NULL, N’Rădăcină’)
 
INSERT INTO Arbore (ID_NodParinte, Informatie)
VALUES (1, N’Nivelul 1 – primul nod’)
 
INSERT INTO Arbore (ID_NodParinte, Informatie)
VALUES (1, N’Nivelul 1 – al 2-lea nod’)
 
INSERT INTO Arbore (ID_NodParinte, Informatie)
VALUES (3, N’Nivelul 2 – primul nod’)
 
INSERT INTO Arbore (ID_NodParinte, Informatie)
VALUES (4, N’Informatie căutată’)
GO
Am creat şi populat o tabelă în care nodul părinte referă o valoare din cheia tabelei. Această structură ajută la reprezentarea unui arbore definit unic printro cheie, părinte şi informaţie (valoare). Ne propunem să aflăm toate nodurile prin care se trece de la rădăcină până la frunza cu o anumită informaţie. Un exemplu mai concret ar fi acela prin care dorim să aflăm toţi şefii unui angajat plecând de la seful imediat superior până la directorul general sau putem afla structura ierarhică a unui departament pornind de la acesta şi până la ultimul nivel de conducere.
Utilizare CTE pentru exemplul propus (frunza cu o informaţie dată):
WITH Arb (ID_Nod, ID_NodParinte, Informatie, Nivel) AS
(
      SELECT ID_Nod, ID_NodParinte, Informatie, 0
      FROM dbo.Arbore
      WHERE Informatie = N’Informatie căutată’
     
      UNION ALL
     
      SELECT a.ID_Nod, a.ID_NodParinte, a.Informatie, Nivel + 1 
      FROM dbo.Arbore a
      INNER JOIN Arb b ON b.ID_NodParinte = a.ID_Nod
      WHERE B.ID_NodParinte IS NOT NULL
)
SELECT ID_Nod, ID_NodParinte, Informatie, Nivel
FROM Arb
ORDER BY ID_Nod
 
Să analizăm:
Se crează membrul ancoră în care se crează o pseudocoloană (Nivel) ce ne va ajuta să identificăm nivelul ierarhic; rezultatul membrului ancoră se foloseşte ca intrare pentru membrul recursiv; se incrementează nivelul; se afişează rezultatul:
ID_Nod      ID_NodParinte Informatie                  Nivel
———– ————- ————————— ——–
1           NULL          Rădăcină                    3
3           1             Nivelul 1 – al 2-lea nod    2
4           3             Nivelul 2 – primul nod      1
5           4             Informatie căutată          0
 
(4 row(s) affected)

Executarea de interogari peste mai multe servere (multiple server query execution).

SQL Server permite executarea de script-uri peste mai multe servere fara a fi necesara schimbarea conexiuni si rerularea sciptului. Pentru a intelege mai bine despre ce este vorba, am sad au un exemplu: vrem sa rulam un script in contextul mai multor baze de date aflate pe servere diferite. Avem mai multe solutii insa spre exemplificare am sa ofer numai una: scriptul respective il putem salva ca procedura stocata in fiecare din bazele de date, fiecare server sa-l adaugam ca linked server intr-o instanta separate de SQL Server iar in aceasta instant sa cream o noua procedura care executa procedura create mai devreme pe fiecare server. Principalul dezavantaj este acela ca daca avem nevoie sa modificam procedura stocata, va trebui facut deployment pe fiecare server  in parte. Aici ne putem ajuta de executarea de interogari asupra mai multor  servere sau mai bine zis asupra unui grup de servere. Vom crea un grup de servere, vom adauga serverele in grup si apoi vom executa scriptul. Pentru aceasta deschidem Management Studio iar apoi pentru a crea un grup de servere putem apasa combinatia de taste CTRL+ALT+G sau mergem in meniul VIEW de unde alegem Registered Servers. Click dreapta pe Local Server Groups si vom selecta New Server Group, introducem denumirea noului grup. Click dreapta pe Local Servers Groups si alegem New Registration Server Registration. In fereastra nou deschisa selectam instant pe care vrem sa o adaugam in grup, setam elementele de securitate si numele instantei eventual si descrierea iar apoi din tab-ul Connection Properties  trebuie sa selectam baza de date in al carei context se va executa scriptul. Repetam acesti pasi pentru fiecare server. Dupa ce am adaugat toate instantele de SQL Server suntem gata sa executam scriptul. Click dreapta pe denumirea grupului si alegem New Query. In fereastra deschisa vom scrie:

SELECT DB_NAME() AS BazaDeDateContextuala

Acesta interogare va avea ca efect afisarea bazei de date curente, veti observa ca desi in interogare este o singura coloana, rezultatul va avea 2 coloane si anume coloanele “Server Name” si “bazaDeDateContextuala” iar rezultatul rularii va contine randuri pentru fiecare server in parte (denumirea server-ului de unde vin randurile este trecuta in prima coloana)