pondelok, 21 júl 2014 14:56 Written by 1401 times
Rate this item
(2 votes)

PL/SQL - optimalizácia príkazu UPDATE v cykle

Príklad som testoval na prostredí ORACLE 11g. Predpokladám, že programátor má základné znalosti v SQL.

Ukážeme si tentokrát viaceré spôsoby, ako co najviac optimalizovane urobiť zmenu hodnôt v riadku, ktorá sa vykonáva v cykle.

Vytvoríme si v testovacej schéme tabuľku zamestnancov nasledovným príkazom: 

CREATE TABLE TEST_EMPLOYEE
(
  ID       NUMBER                               NOT NULL,
  NAME     VARCHAR2(20)                         NOT NULL,
  SURNAME  VARCHAR2(50)                         NOT NULL,
  SALARY   NUMBER                               NOT NULL
);


Podstatný je pre nás plat, ako vždy:) a potom si naplníme tabuľku dátami (zamestnancami):

 

BEGIN
  INSERT INTO TEST_EMPLOYEE(ID,NAME,SURNAME,SALARY) VALUES (1,'Peter','Maly',3500);
  INSERT INTO TEST_EMPLOYEE(ID,NAME,SURNAME,SALARY) VALUES (1,'Juraj','Velky',1200);
  INSERT INTO TEST_EMPLOYEE(ID,NAME,SURNAME,SALARY) VALUES (1,'Maria','Drobna',800);
  COMMIT;
END;

 

Vytvoríme si primárny kľúč na tabuľke:

 

ALTER TABLE TEST_EMPLOYEE ADD (CONSTRAINT EMPLOYEE_PK PRIMARY KEY (ID));

 

Rozhodli sme sa, že zvýšime všetkým zamestnancom plat o 500 Eur v hrubom (aby sme aspoň trošku dobehli platy upratovačiek v susednom Rakúsku), ktorý ho majú nižší ako 3000 Eur.
Napísal som tri podobné anonymné PL/SQL bloky vykonávajúce tú istú vec, ale podstatné si bude všimnúť, ktorý z nich je najefektívnejší.
Odmeral som čas výkonu každého bloku, aby sme si overili efektivitu každého bloku zvlášť. Pri vložení viacerých záznamov do tabuľky (rádovo v tisíckach) sú rozdiely samozrejme ešte výraznejšie.

 

-- 1. príklad 114 ms ...
DECLARE
    CURSOR EMP_CURSOR IS 
        SELECT ID, SALARY 
        FROM TEST_EMPLOYEE 
        WHERE SALARY<3000;
BEGIN
    FOR EMP_ROW IN EMP_CURSOR
    LOOP
        UPDATE TEST_EMPLOYEE
        SET SALARY = SALARY + 500
        WHERE ID=EMP_ROW.ID;
    END LOOP;
    COMMIT; 
END;

 

Najpoužívanejší (najpomalší ale najbezpäcnejší) spôsob je práve tento. Po vytvorení kurzora sa v cykle robí opätovné hľadanie príkazom UPDATE na tej istej tabuľke, ale hľadanie je prevedené na primárnom kľúči v inom kurzore.
Môže byť ešte pomalší, ak vymažeme primárny klúč na tabuľke TEST_EMPLOYEE. V tom prípade je hladanie prevedené tzv. "FULL SCANOM" sekvenčným prehľadávaním v inom kurzore.

 

-- 2. príklad 110 ms ...
DECLARE
    CURSOR EMP_CURSOR IS 
        SELECT ROWID, SALARY 
        FROM TEST_EMPLOYEE 
        WHERE SALARY<3000;
BEGIN
    FOR EMP_ROW IN EMP_CURSOR
    LOOP
        UPDATE TEST_EMPLOYEE
        SET SALARY = SALARY + 500
        WHERE ROWID=EMP_ROW.ROWID;
    END LOOP;
    COMMIT; 
END;

 

V tomto prípade využijeme na hľadanie nie hodnotu primárneho kľúča - stlpec "ID", ale pseudostĺpec "ROWID", pomocou ktorého je hľadanie riadkov najrýchlejšie.
Nevýhoda: V prípade použitia tohto spôsobu si buď sme istý, že nikto iný nepristupuje k našej tabulke a nevykonáva na nej žiadny DML príkaz, alebo si tabulku alebo riadky explicitne uzamkneme. Napríklad príkazom "LOCK TABLE / SELECT FOR UPDATE".
Je to z dôvodu, že hodnoty v pseudostl'pci "ROWID" sa môžu zmeniť v závislosti, aká zmena bola v riadku vykonaná.

-- 3. príklad 95 ms ...
DECLARE
    CURSOR EMP_CURSOR IS 
        SELECT ID, SALARY 
        FROM TEST_EMPLOYEE 
        WHERE SALARY<3000
        FOR UPDATE;
BEGIN
    FOR EMP_ROW IN EMP_CURSOR
    LOOP
        UPDATE TEST_EMPLOYEE
        SET SALARY = SALARY + 500
        WHERE CURRENT OF EMP_CURSOR;
    END LOOP;
    COMMIT; 
END;

 

Tento spôsob je najrýchlejší a najefektívnejší. Príčina efektivity spočíva v tom, že sa nevytvára další kurzor a príkaz "UPDATE" sa vykonáva práve na aktuálnom riadku - klauzula "CURRENT OF".
Nevýhody tohto spôsobu: kôli klauzule "FOR UPDATE" sa uzamknú všetky riadky, na ktorých má byt vykonaná zmena.

Prajem vela efektívneho a bezchybného kódu.

S pozdravom

Last modified on štvrtok, 24 júl 2014 09:04
Alojz Benďák

Autor je administrátor webu a venuje sa programovaniu takmer 20 rokov.



  • prvý kontakt s počítačmi na strednej škole - PMD 1,2,3, PP06, Atari, Sinclair (programovanie v jazyku: strojový kód, basic a pascal):  2 roky
  • na VŠ - jazyky Turbo pascal a Turbo C: 1rok
  • programovanie databázového ekonomického širokoškálneho software v Delphi a C++ Borland (databázy Paradox): 8 rokov v Codex s.r.o. Nitra
  • programovanie webových aplikácií na databázach MySQL a MS SQL server: 1 roky na živnosť (firmy: Hermes Nitra, Schindler v Bratislave)
  • pracoval ako DBA v Homecredit Brno a vo VÚB Bratislava ako vyvojár + optimalizácie v SQL a PL/SQL na Oracle 11g (firma Accenture)
  • programoval programy pre súkromné firmy pre HW na skenovanie povrchov nádrží pre skladovanie tekutých palív (pristroj Leica)
  • momentálne pracuje ako DBA pod Oracle 11g a ako vyvojár vnútropodnikových databázových aplikacií v PHP, SQL, C++, C# a interface v PL/SQL: 8 rokov
  • ďalej programuje s HTML, CSS, Ajax, Javascript, VBA, MS visual C++ a C# malé podporné aplikácie
  • certifikát SQL expert for Oracle 11g   
  • spolupracuje a aktívne učí pre počítačové firmy: Lapis, IVIT v Nitre