SQL Magazin 7 articol – SQL Server: Turbinați-vă interogările cu ajutorul vizualizărilor indexate

admin 0 Comments Articles

SQL Magazin 7 articol – SQL Server: Turbinați-vă interogările cu ajutorul vizualizărilor indexate

Rețineți întrebarea Marcați ca fiind completată Adnotați

Scopul acestui articol este de a introduce conceptul de vizualizări indexate SQL Server și de a arăta cum să implementați și să utilizați acest tip de vizualizare pentru a optimiza interogările.

Conceptul de vizualizare

Visualizările sunt, de asemenea, cunoscute sub numele de „tabele virtuale”, deoarece ele prezintă o alternativă la utilizarea tabelelor pentru a accesa datele. O vizualizare nu este nimic mai mult decât o instrucțiune SELECT încapsulată într-un obiect. Sintaxa pentru crearea unei vizualizări este cea prezentată în Lista 1.

CREATE VIEW nome_da_visão ...) ] AS subconsulta;

Vezi un exemplu de creare și utilizare a unei vizualizări în Lista 1.

Use NorthWind go create view vi_vendas_mes As Select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity) from Orders o inner join od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate), datepart(mm,o.OrderDate) go select * from vi_vendas_mês go ano mes qtde_total contador ----------- ----------- ----------- -------------------- 1996 7 1462 59 1996 8 1322 69 1996 9 1124 57 1996 10 1738 73 1996 11 1735 66

Printre avantajele utilizării vizualizărilor, putem menționa :

• Simplificarea codului: puteți scrie SELECT-uri complexe o singură dată, le puteți încapsula în vizualizare și le puteți declanșa din aceasta, ca și cum ar fi vorba de orice tabel;
• Probleme de securitate: să presupunem că aveți informații confidențiale în anumite tabele și, prin urmare, doriți ca numai unii utilizatori să aibă acces la ele. Cu toate acestea, unele coloane din aceste tabele trebuie să fie accesate de toți utilizatorii. O modalitate eficientă de a rezolva această problemă este de a crea o vizualizare și de a ascunde coloanele sensibile. În acest fel, se pot suprima drepturile de acces la tabelul original și se poate elibera accesul la vizualizare;
• Posibilitatea de optimizare a interogărilor prin implementarea vizualizărilor indexate.

Vizualizări indexate în practică

Vizualizările încapsulează instrucțiuni SELECT, ceea ce înseamnă că, ori de câte ori sunt declanșate, sunt executate instrucțiunile SELECT asociate cu acestea. Vizualizările nu creează depozite pentru datele pe care le returnează (așa cum face tabelul). Ei bine, ar fi grozav dacă am putea „materializa” într-un tabel rezultatul comenzii SELECT găsit în view, creând indici pentru a facilita accesul la acesta. Ei bine, vizualizările indexate fac exact acest lucru. Executarea unui SELECT într-o vizualizare indexată are același efect ca și executarea unui select într-o tabelă convențională.

Scopul principal al vizualizărilor indexate este de a crește performanța, iar avantajul SQL Server este de a permite planurilor de execuție să ia în considerare vizualizarea indexată ca mijloc de accesare a datelor, chiar dacă numele vizualizării nu a fost explicit în interogare. Acest lucru este posibil în ediția Enterprise a SQL Server 2000, unde optimizatorul de comenzi poate selecta datele direct în vizualizarea indexată (în loc să selecteze datele brute existente în tabel), după cum vom vedea în continuare.

Crearea pas cu pas a unei vizualizări indexate

1. Configurarea mediului, primul pas este să setați starea unor parametri în sesiunea în care doriți să creați și să folosiți vizualizarea, deoarece cum vizualizarea indexată este „materializată” într-un tabel, nimic nu poate interfera cu rezultatul ei. Imaginați-vă, de exemplu, următorul scenariu:
2. O anumită setare, care afectează rezultatul unui SELECT (de exemplu, concat_null_yelds_null), este setată înainte ca vizualizarea indexată să fie creată;
3. Visualizarea indexată este creată; rețineți că rezultatul vizualizării va fi „materializat” pe disc în conformitate cu setarea setată în etapa anterioară;
4. Apoi setarea este dezactivată și vizualizarea indexată este executată de către utilizator. Deoarece vizualizarea a fost materializată, vom obține un rezultat care nu este în concordanță cu configurația curentă.

De exemplu, lista 2 arată diferența în rezultatul unei comenzi atunci când proprietatea concat_null_yields_null este modificată.

set concat_null_yields_null ON print null + 'abc' -------------------------------------- Set concat_null_yields_null OFF print null + 'abc' -------------------------------------- abc

Imaginați-vă ce s-ar întâmpla dacă vizualizarea indexată a fost creată cu proprietatea concat_null_yields_null activată, dar sesiunea curentă a fost cu această proprietate dezactivată – același SELECT ar duce la rezultate diferite!

Această problemă a fost rezolvată într-un mod simplu – pentru a crea și utiliza vizualizări indexate, este obligatoriu să se configureze mediul în conformitate cu o listă de valori implicite. În acest fel, este imposibil să se obțină rezultate diferite, deoarece vizualizarea pur și simplu nu va funcționa dacă oricare dintre setări este setată la o valoare non-standard.

Tabelul 1 afișează aceste setări și valorile lor implicite respective.

(*) Id-ul este utilizat în comanda sp_configure. Pentru a verifica ce face fiecare setare, citiți secțiunea „Setări necesare pentru vizualizările indexate”. Există două moduri de a modifica valoarea unei configurații:

• Direct în sesiune: executați comanda set ON | OFF
• Modificarea valorii implicite existente pe server: executați sp_Configure ‘user options’, . Numărul de identificare al fiecărei configurații poate fi văzut în tabelul 1.
  Notă: AritHabort are id 64 și Quoted_Identifier are id 256. Pentru a lega, de exemplu, Quoted_Identifier + AritHabort, vom executa sp_cofigure, trecând ca parametru rezultatul de 64+256 (=320): sp_configure ‘user options’, 320. Pentru o listă completă a id-urilor atribuite fiecărei configurații, mergeți la http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_9b1q.asp.

Pentru a confirma starea fiecăruia dintre parametrii din tabelul 1, utilizați funcția SessionProperty(‘nume parametru’) sau comanda DBCC UserOptions.

În acest fel, setați toți parametrii în conformitate cu coloana „required state for indexed views” din tabelul 1 – dacă nu se face acest lucru, SQL Server nu vă va permite să creați/executați vizualizarea indexată.

Crearea vizualizării indexate

Vom crea o vizualizare pentru a totaliza suma zilnică vândută în tabelul Order Details, aflat în baza de date NorthWind. Vezi lista 3.

use NorthWind go create view vi_vendas_mes with SchemaBinding as select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity), contador = count_big(*) from dbo.Orders o inner join dbo. od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate),datepart(mm,o.OrderDate) go

Trebuie să respectăm câteva particularități atunci când creăm vizualizări indexate:

• Vizualizarea trebuie să fie deterministă. O vizualizare va fi deterministă dacă folosim numai funcții deterministe în codul său. Aceeași comandă SELECT executată în mod repetat pe o vizualizare indexată (având în vedere o bază statică) nu poate da rezultate diferite. Funcțiile deterministe asigură faptul că rezultatul unei funcții va rămâne neschimbat indiferent de câte ori este executată. Funcția DatePart, de exemplu, este deterministă, deoarece returnează întotdeauna același rezultat pentru o anumită dată. Funcția getdate() va returna o valoare diferită de fiecare dată când este executată. Pentru lista completă a funcțiilor deterministe SQL Server 2000, mergeți la http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_95v7.asp
• Verificați dacă nu există restricții de sintaxă. Clauzele, funcțiile și tipurile de interogări enumerate mai jos nu pot integra schema unei vizualizări indexate:
  • MIN, MAX,TOP
  • VARIANTA,STDEV,AVG
  • COUNT(*)
  • SUM pe coloane care permit valori nule
  • DISTINCT
  • Funcția
  • ROWSET
  • Tabele derivate, îmbinări proprii, subinterogări, outer joins
  • DISTINCT
  • UNION
  • Float, text, ntext și imagine
  • COMPUTE și COMPUTE BY
  • HAVING, CUBE și ROLLUP

Este necesar să se creeze vizualizările indexate cu SchemaBinding. Pentru a păstra consecvența conținutului vizualizării, nu puteți modifica structura tabelelor care au dat naștere vizualizării. Pentru a evita acest tip de problemă, este obligatoriu să utilizați SchemaBinding atunci când creați vizualizări indexate, deoarece această opțiune nu vă permite să modificați structura tabelului fără a șterge mai întâi vizualizarea.

Pentru a utiliza clauza GROUP BY, este obligatoriu să includeți funcția COUNT_BIG(*). Funcția count_big(*) face același lucru ca și count(*), dar returnează o valoare de tip bigint (8 octeți).

Informați întotdeauna proprietarul obiectelor la care se face referire în vizualizarea indexată. Utilizați select * from dbo.Orders în loc de select * from Orders, deoarece este posibil să aveți tabele cu același nume, dar cu proprietari diferiți. Deoarece opțiunea schemabinding este obligatorie, SQL Server are nevoie de specificația exactă a obiectului pentru a frâna modificarea schemei.

Crearea unui index cluster în vedere (materializare)

Vederea creată la punctul 2 nu se comportă încă ca o vedere indexată, deoarece rezultatul comenzii select nu a fost materializat într-un tabel. Puteți confirma această afirmație executând comanda sp_spaceused în Query Analyzer, care returnează numărul de rânduri și spațiul utilizat de tabele (Listarea 4).

sp_SpaceUsed vi_vendas_mes -------------------------------------------------------------------------------------- Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91 Views do not have space allocated.

Listarea 4. Utilizarea comenzii sp_spaceused pe o vizualizare

Observați în Listarea 5 că procesarea vizualizării este pur logică, atât de mult încât valoarea lui Physical Reads este zero. Observați valorile înregistrate în Logical Reads și Physical Reads (1672+0+4+0=1676) – vom folosi aceste valori în comparațiile noastre viitoare.

Set statistics io ON Select * from vi_vendas_mesgo---------------------------------------------------------ano mes qtde_total contador ----------- ----------- ------------- -------------------- 1996 7 1462 591996 8 1322 691996 9 1124 57.....(23 row(s) affected)Table 'Order Details'. Scan count 830, logical reads 1672, physical reads 0, read-ahead reads 0.Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

Lista 5. Total I/O asociat vizualizării ÎNAINTE de crearea indexului cluster

Pentru a verifica dacă vizualizarea poate fi indexată (materializată), adică dacă a fost creată în conformitate cu standardele și setările necesare pentru vizualizările indexate, rezultatul următorului SELECT ar trebui să fie egal cu 1.

select ObjectProperty(object_id('vi_vendas_mes'),'IsIndexable')

Confirmând condițiile prealabile, putem acum să creăm indexul. Sintaxa are același format utilizat la crearea de indici în tabele convenționale:

create unique clustered index pk_ano_mes on vi_vendas_mes (ano,mes)

Rețineți că indicele cluster este necesar deoarece generează pagini de date. Puteți crea indici non-cluster în vizualizări indexate numai după ce ați creat indicele cluster.

Acum SELECT-ul găsit în vizualizare a fost materializat. ceea ce poate fi dovedit cu comanda sp_spaceused din Query Analyzer (Listarea 6).

sp_SpaceUsed vi_vendas_mêsgo-----------------------------------------------------Name Rows Reserved data index_size Unusedvi_vendas_mes 23 24 KB 8 KB 16 KB 0 KB

Listarea 6 . Utilizarea comenzii sp_spaceused pe o vizualizare indexată

Utilizarea vizualizărilor indexate

O modalitate de a accesa o vizualizare indexată (precum și o vizualizare convențională) este de a face referire la numele acesteia în comanda SELECT:

select * from vi_vendas_mes

Comparați volumul de pagini mutate în lista 5 (1672+4=1676) cu cel din lista 7 (2+0=2). Diferența este destul de semnificativă – crearea vizualizării indexate a redus I/O total necesar cu 1674 de pagini.

Set statistics io ON select * from vi_vendas_mes go --------------------------------------------------------- ano mes qtde_total contador ----------- ----------- ------------- -------------------- 1996 7 1462 59 1996 9 1124 57 ..... (23 row(s) affected) Table 'vi_vendas_mes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Lista 7. I/O total asociat vizualizării după crearea clusterului de indici

Să ne uităm la un alt exemplu. Figura 1 prezintă planul de execuție al unei interogări. Confirmați că vizualizarea a fost selectată chiar dacă nu era prezentă în rândul SELECT.

În timpul construirii planului de execuție a interogării, optimizatorul a constatat că existau deja date pre-sumarizate pentru interogare în vi_sales_mes și a ales să selecteze datele direct în vizualizarea indexată.

Rețineți că interogarea executată în figura 1 este identică cu cea găsită în vizualizarea vi_sales_mes. Cu toate acestea, accesul la vizualizare de către optimizator este independent de similaritatea dintre interogarea executată și interogarea vizualizării. Selectarea vizualizării indexate de către procesorul de interogare ia în considerare doar raportul cost-beneficiu. În acest fel, nu este necesar ca interogările executate să fie identice cu vizualizarea (a se observa figura 3).

Cu toate acestea, este necesar să se respecte anumite reguli pentru ca vizualizarea indexată să fie luată în considerare de către optimizatorul de interogări:

• Joncțiunea prezentă în vizualizare trebuie să fie „conținută” în interogare: dacă interogarea realizează o îmbinare între tabelele A și B, iar vizualizarea realizează o îmbinare între A și C, vizualizarea nu va fi declanșată în planul de execuție. Cu toate acestea, dacă interogarea efectuează o îmbinare între A,B și C, vizualizarea poate fi declanșată.
• Condițiile stabilite în interogare trebuie să fie în concordanță cu condițiile din vizualizare: în SELECT din figura 2, vizualizarea indexată vi_sales_mes nu va fi luată în considerare deoarece clauza where nu a fost prezentă în codul vizualizării, ceea ce a făcut ca rândurile cu Cantitatea <= 5 să fie calculate în îmbinare.

Pe de altă parte, dacă interogarea are condiția where where sum(Quantity) > 5, vizualizarea vi_sales_mes va fi luată în considerare în planul de execuție, deoarece condiția de interogare este un subset al SELECT prezent în vizualizare.

Columnele cu funcții de agregare din interogare trebuie să fie „conținute” în definiția vizualizării: dacă vizualizarea returnează coloana qtde=sum(Cantitate) și interogarea are o coloană vlr_unit=sum(PrețUnitate), vizualizarea nu va fi luată în considerare.

Figura 3 prezintă o comandă SELECT care permite să demonstreze inteligența optimizatorului de comenzi – calculul AVG(Quantity) a fost înlocuit cu împărțirea SUM(Quantity) / Count_Big(*), reprezentată prin pictograma Compute Scalar. Se ia în considerare și predicatul where sum(Quantity) > 1500 (reprezentat prin pictograma Filter).

Considerații generale despre utilizarea vizualizărilor indexate:

• Visualizările indexate pot fi create în orice versiune de SQL Server 2000. Cu toate acestea, numai în versiunea Enterprise Edition vor fi selectate automat de către optimizatorul de interogări.
• În alte versiuni decât Enterprise, trebuie să utilizați indicatorul NoExpand pentru a accesa vizualizarea indexată ca un tabel convențional. Dacă opțiunea NoExpand nu este utilizată, vizualizarea indexată va fi considerată o vizualizare „normală”.
  Notă: Indicația Expand face invers decât NoExpand: tratează vizualizarea indexată ca pe o vizualizare „normală”, forțând SQL Server să execute instrucțiunea SELECT în timpul fazei de execuție.
• Întreținerea unei vizualizări indexate este automată (ca și în cazul indexurilor); nu necesită nici o sincronizare suplimentară. Prin caracteristica sa proprie (de obicei stochează date pre-sumate), actualizarea sa tinde să fie puțin mai lentă decât cea a indexurilor convenționale.
• Utilizarea vederilor indexate în bazele OLTP necesită prudență, deoarece, deși prezintă performanțe mari în interogări, ele cauzează suprasolicitare în procesele care modifică tabelele legate în vedere. În situațiile în care este necesară o performanță ridicată de scriere, cu actualizări frecvente, nu se recomandă crearea de vizualizări indexate.
• Ca și în cazul indexurilor, optimizatorul va analiza codul vizualizărilor din versiunile Enterprise ca parte a procesului de alegere a celui mai bun plan de execuție pentru o interogare. Cu toate acestea, în cazul în care există mai multe vizualizări indexate care pot fi executate pentru aceeași interogare, este posibil să se înregistreze o creștere substanțială a timpului de alegere, deoarece toate vizualizările vor fi analizate. Prin urmare, folosiți bunul simț atunci când implementați vizualizările.

Setări necesare pentru vizualizările indexate

ANSI_NULLS

Define modul în care sunt efectuate comparațiile cu valori nule (Listarea 8).

set ANSI_NULLS ON declare @var char(10) set @var = null if @var = null print 'VERDADEIRO' else print 'FALSO' ------------------------------------- FALSO set ANSI_NULLS OFF declare @var char(10) set @var = null if @var = null print 'VERDADEIRO' else print 'FALSO' -------------------------------------- VERDADEIRO

Listarea 8. Exemple de ansi_null

ANSI_PADDING

Determină modul în care trebuie stocate coloanele char, varchar, binary și varbinary atunci când conținutul lor este mai mic decât dimensiunea definită în structura tabelului. Standardul SQL Server 2000 este de a menține ansi_padding activat (=ON); în această condiție se aplică următoarele reguli:

• Când se actualizează coloanele char, spațiul alb va fi adăugat la sfârșitul șirului dacă acesta este mai mic decât dimensiunea definită în structura coloanei. Aceeași regulă se aplică și în cazul coloanelor binare (în acest caz, spațiul este umplut cu o secvență de zerouri)
• Coloanele Varchar sau varbinare nu respectă regula de mai sus: acestea își păstrează întotdeauna dimensiunea inițială.

ARITHABORT

Când este activat, termină execuția interogării la întâlnirea unei diviziuni cu zero sau a unui tip de depășire.

QUOTED_IDENTIFIER

Când este activat, permite utilizarea ghilimelelor duble pentru a specifica numele tabelelor, coloanelor etc. – În acest fel, aceste nume pot avea spații și sau caractere speciale.

CONCAT_NULL_YELDS_NULL

Controlează rezultatul concatenării șirurilor de caractere cu valori nule. Atunci când este activată, determină faptul că această îmbinare trebuie să returneze o valoare nulă; în caz contrar, va returna șirul însuși.

ANSI_WARNINGS

Când este activată, determină generarea de mesaje de eroare atunci când:

1. utilizați funcții de sumarizare și se găsesc valori nule în intervalul de interogare;
2. se găsesc diviziuni prin zero sau depășiri aritmetice.

NUMERIC_ROUNDABORT

Controlează modul în care SQL Server ar trebui să procedeze atunci când întâlnește o pierdere de precizie numerică în operațiile aritmetice. Dacă parametrul este activat și o variabilă cu o precizie de două zecimale primește o valoare cu trei zecimale, operațiunea va fi întreruptă. Dacă parametrul este dezactivat, valoarea va fi trunchiată la două zecimale.

Concluzie

Când vine vorba de optimizarea interogărilor, vizualizările indexate sunt o alegere bună pentru a profita de performanță. Așadar, evaluați temeinic interogările care se referă la rezumări și care sunt executate cu o anumită frecvență și optați pentru crearea de vizualizări indexate. Rezultatul merită!