Managing Hierarchical Data in MySQL

Exemple de generare categoriilor ierarhic din SQL

exemplu:

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = ‘TELEVISIONS’;

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES(‘GAME CONSOLES’, @myRight + 1, @myRight + 2);

UNLOCK TABLES;

SELECT CONCAT( REPEAT(‘ ‘, COUNT(parent.name) – 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+———————–+
| name |
+———————–+
| ELECTRONICS |
| -TELEVISIONS |
| – -TUBE |
| – -LCD |
| – -PLASMA |
| -PORTABLE ELECTRONICS |
| – -MP3 PLAYERS |
| – -FLASH |
| –CD PLAYERS |
| –2 WAY RADIOS |
+———————–+

Soursa: aici

Optimizari la migrarea unei baze de date pe SQL 2008

In multe situatii cand se migreaza o baza de date de pe un server pe altul (in special cand este vorba de o migrare de pe o versiune inferioara de Microsoft SQL cum ar fi SQL 2000 sau SQL 2005) puteti avea surpriza neplacuta ca query-urile si procedurile existente sa va mearga mult mai lent. Este vorba despre o migrare prin backup si restore. De obicei se migreaza pe un server mai nou, mai puternic si mai recent pentru a obtine un spor de performanta. Si, supriza, ajungeti sa descoperiti ca merge mai greu decat serverul vechi. In cazul nostru, erau de exemplu niste proceduri care pe serverul vechi (MS SQL 2000) se executau in aproximativ 11 de secunde si pe serverul nou (MS SQL 2008) ajungeau sa se execute in mai mult de 2 minute. Ce era mai interesant, este ca timpul de executie varia aleator, intre 1:40 minute si 2:30 minute. Aceasta situatie ajungea sa genereze timeout-uri si probleme.

Solutia sau mai bine zis solutiile sunt de fapt optimizarea pentru SQL 2008 prin setarea compatibilitatii, refacerea statisticilor si a index-urilor pe tabele si recompilarea procedurilor. Nu este necesar nici un fel de schimbare de cod, trebuie doar sa umblati la niste optiuni si sa rulati niste comenzi. Prezentat mai in detaliu, trebuie facuti urmatorii pasi:

MS SQL Server 2008

Pasul 1: Setarea AWE pe Server si Minimum respectiv Maximum Server Memory (necesar doar in anumite configuratii, in special cele pe 32 de biti, pentru mai multe detalii vedeti AICI)

Pasul 2: Schimbarea compatibilitatii bazei de date pe SQL 2008.
Pentru a beneficia la maxim de avantajele si imbunatatirile oferite de MS SQL 2008, daca faceti migrarea de pe o versiune mai veche de MS SQL, trebuie setat nivelul comatibilitatii bazei de date pe 2008. Pentru asta dati click dreapta -> Properties in SQL Management Studio pe baza de date noua pe care ati dat restore. Aici schimbati in Options, Compatibility Level pe valoarea “SQL Server 2008 (100)”. Nu trebuie sa va faceti griji caci tot ce ati facut pana acuma va fi compatibil in continuare fiind foarte situatiile cand aceasta setare ar putea cauza probleme.

Pasul 3: Recompilare si actualizarea statisticilor (conform discutiei de AICI)
Acesta mi se pare pasul cel mai important. Refacerea index-ului si a statisticilor pe tabele, reorganizeaza datele pe noul server, le adapteaza conform versiunii mai noi si optimizeaza rularea query-urilor in special in conjunctura cu index-urile pe tabele. Recompilarea procedurilor (care prin comanda de mai jos se va realiza la prima rulare), optimizeaza procedurile si triggerele conform schimbarilor in statistici si in index-uri si astfel le reorganizeaza sa ruleze mai bine. Cel mai bine ar fi sa se ruleze comanda de refacere a index-ului si a statisticilor, respectiv recompilarea, si dupa ceva timp de folosire cand chiar exista niste date relevante, un istoric si dinamism al tabelelor pe baza carora sa genereze niste statistici relevante, sa ajusteze index-ul corespunzator si evident si procedurile care le folosesc. Detalii suplimentare si o explicatie pe exemplu despre index si statistici gasiti AICI, si continuarea AICI.

Practic trebuie facute 3 lucruri ca sa functioneze totul optim:
– trebuie recompilate tabelele, functiile si procedurile folosind comanda sp_recompile (mai multe detalii despre sintaxa comenzii AICI)
– trebuie refacut indexul tabelelor folosind comanda DBCC DBREINDEX (mai multe detalii despre sintaxa comenzii AICI)
– trebuie actualizate statisticile tabelelor folosind comanda UPDATE STATISTICS cu optiunea WITH FULLSCAN (mai multe detalii despre sintaxa comenzii AICI)
Acesti pasi sunt necesari pentru ca, cele 3 operatii actualizeaza toate informatiile legate de index, statistici si cum sunt compilate datele pentru serverul nou. Conform recomandarii unui prieten mai cunoscator in domeniu ar trebui de fapt dat atat CREATE STATISTICS cat si UPDATE STATISTICS.

Dupa ce am trecut prin toti pasii de mai sus am ajuns sa avem o optimizare la executarea unei proceduri de la peste 2 minute la numai 2 secunde.

O alta recomandare ar fi sa studiati Execution Plan-ul pe toate query-urile si procedurile care dureaza mult si sa vedeti unde puteti optimiza. De exemplu, in situatia noastra, am observat ca sub SQL 2008 (comparativ cu SQL 2000) o functie care lua date generale despre o persoana de mai multe ori de-a lungul mai multor pasi imbricati ai unor proceduri, ajunge sa ingreuneaze executia. Cand nu am mai folosit acea functie ci join-uri explicite cu tabelele implicate am ajuns la injumatatirea anumitor timpi de executie.

Sper sa va ajute ce v-am explicat pana acuma si va urez sa aveti exec-uri si select-uri cat mai rapide 🙂

LATER EDIT:
Operatiile descrise mai sus de refacere a index-ului si statisticilor unui tabel sunt necesare si in situatia in care se sterg toate intrarile dintr-un tabel si se face un insert rezultat dintr-un select pe alt tabel (nu conteaza daca e in cadrul aceeasi baze de date sau din alta baza de date). Acelasi lucru este valabil daca se actualizeaza multe valori, prin update (peste 40% din totalul randurilor din tabelul respectiv) care au un index asociat.
Din pacate Microsoft SQL Server nu isi da seama automat ca s-a schimbat total continutul tabelului si nu isi recalculeaza aceste valori automat rezultand in niste query-uri care dau erori de timeout (cel putin in cazul apelarii din .Net).