Szybciej nie będzie czyli jak (nie) używać bulk collect.

Pewnego razu analizowałam sprawiający problemy wydajnościowe system. Zauważyłam, że dane przetwarzane są wiersz po wierszu. Moją pierwszą propozycją było zastosowanie BULK COLLECT.

Ku mojemu zdziwieniu programista powiedział, że szybciej nie będzie, bo w systemie jest już używany BULK COLLECT. Zaskoczył mnie tym, nie powiem. Kod wyglądał na klasyczne przetwarzanie wiersz po wierszu. Postanowiłam sprawdzić to jeszcze raz. Programista miał rację! Do pobierania wierszy używana była metoda BULK COLLECT.

Dlaczego więc kod wyglądał jak przetwarzanie wiersz po wierszu?

Dlaczego więc system był wolny, mimo najlepszych optymalizacyjnych technik?

Analiza kodu dość szybko przyniosła odpowiedź na te pytania. Otóż zapytania wykorzystujące metodę BULK COLLET pobierało jeden wiersz. W pętli. Wiersz po wierszu.

Czy takie użycie BULK COLLECT ma sens?

Jak przerobić kod przetwarzający rekordy wiersz po wierszu na proces używający metodę przetwarzania masowego?

Sprawdźmy.

Testy przeprowadziłam na bazie Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production.

Do testów użyłam predefiniowanego schematu HR.

Utworzyłam testową tabelę EMP_BULK_TEST na bazie tabeli HR. EMPLOYEES.

 create table emp_bulk_test as  
 with data as (select level lvl from dual connect by level <= 5000)  
 select rownum new_employee_id , count(*) over () cnt, employee_id,lvl,   
     first_name,  
     last_name,  
       email,  
       phone_number,  
       hire_date,  
       job_id,  
       salary,  
       commission_pct,  
       manager_id,  
       department_id  
   from employees, data ;

 alter table emp_bulk_test add (new_salary number);   
 alter table emp_bulk_test add constraint emp_bulk_pk primary key(new_employee_id);   
 create index emp_bulk_idx on emp_bulk_test(department_id);

-- update to clear data before test  
 update emp_bulk_test set new_salary =  null; 
 commit;

W tabeli EMP_BULK_TEST mamy ponad pół miliona rekordów.

Opis testu:

Wszyscy pracownicy departamentów znajdujących się w USA (LOCATIONS.COUNTRY_ID = 'US') dostaną podwyżkę wynoszącą taki procent od pensji (SALARY), jaki mają procent od sprzedaży (COMMISSION_PCT).

W pierwszym teście, klasycznym przetwarzaniu wiersz po wierszu, w pierwszym kroku utworzymy pętlę po departamentach, by wybrać te, znajdujące się w USA.

W następnym kroku wybierzemy id (EMPLOYEE_ID) pracowników poszczególnych departamentów.

Dopiero w trzecim kroku pobierzemy szczegółowe dane pracownika. Na podstawie tych danych wyliczymy nową wartość pensji.

W czwartym kroku nowa pensja zostanie zapisana w tabeli EMP_BULK_TEST dla danego pracownika.

Test 1:

 set serveroutput on  
 declare  
  nSalary emp_bulk_test.salary%type;  
  nNewSalary emp_bulk_test.new_salary%type;  
  ncommission_pct emp_bulk_test.commission_pct%type;  
  nTimeStart number := dbms_utility.get_time;   
  nTimeEnd number;  
  nCnt number :=0 ;
 begin  
  nTimeStart := dbms_utility.get_time;   
  for dept in (select department_id from departments d, locations loc where d.location_id = loc.location_id and country_id = 'US')   
  loop  
   for z in (select new_employee_id from emp_bulk_test where department_id = dept.department_id) loop  
   
     select salary, commission_pct into nSalary, ncommission_pct from emp_bulk_test where new_employee_id = z.new_employee_id;     
     nNewSalary := nSalary + nvl(ncommission_pct, 0.01) * nSalary;  
     update emp_bulk_test set New_Salary = nNewSalary where new_employee_id = z.new_employee_id;    
     commit;  
     nCnt := nCnt +1;
   end loop;  
  end loop;  
  nTimeEnd := dbms_utility.get_time;    
  DBMS_OUTPUT.PUT_LINE('Row by row: '||nCnt ||' ' || trunc( (nTimeEnd - nTimeStart)/100,2) );   
 end;

Row by row: 340000 :55,42

W teście drugim użyjemy BULK COLLECT, nie zmieniając nic poza tym w kodzie.

Test 2.

 set serveroutput on  
 declare  
  nEmp_id number;  
  type tabnum is table of number index by pls_integer;  
  tSalary tabnum;  
  tcomm tabnum;  
  nNewSalary number;  
 ----------------------------------------  
  nTimeStart number := dbms_utility.get_time;   
  nTimeEnd number;   
  nCnt number := 0;  
 begin  
   nTimeStart := dbms_utility.get_time;   
   for dept in (select department_id from departments d, locations loc where d.location_id = loc.location_id and country_id = 'US')   
   loop  
     for z in (select new_employee_id from emp_bulk_test where department_id = dept.department_id) loop  
       select salary, commission_pct bulk collect into tSalary, tComm from emp_bulk_test where new_employee_id = z.new_employee_id;  
       nNewSalary := tSalary(1) + nvl(tcomm(1), 0.01) * tSalary(1);  
       update emp_bulk_test set New_Salary = nNewSalary where new_employee_id = z.new_employee_id;  
       commit;  
       nCnt := nCnt + 1;  
     end loop;  
   END LOOP;  
  nTimeEnd := dbms_utility.get_time;    
  DBMS_OUTPUT.PUT_LINE('Bulk 1:'|| nCnt || ' ' || trunc( (nTimeEnd - nTimeStart)/100,2) );   
 end;

Bulk 1: 340000 56,06

Wersja z BULK COLLECT jest porównywalna pod względem czasu przetwarzania do klasycznej wersji wiersz po wierszu.

Czyli szybciej nie będzie? Czy możemy coś zrobić?

Być może już na początku zwróciliście uwagę, że zapytania w procedurze można dość łatwo połączyć. Trzeba pamiętać, że w tym prostym przykładzie możliwość połączenia zapytań od razu rzuca się w oczy. Jednak w prawdziwym systemie, gdzie zapytania są bardziej skomplikowane a kod podzielony na funkcje i procedury - trzeba będzie się uważniej przyjrzeć by dostrzec taką możliwość.

W trzecim teście połączymy wszystkie zapytania w jedno.

Test 3:

 set serveroutput on  
 declare  
  nSalary emp_bulk_test.salary%type;  
  nNewSalary emp_bulk_test.new_salary%type;  
  ncommission_pct emp_bulk_test.commission_pct%type;  
  nTimeStart number := dbms_utility.get_time;   
  nTimeEnd number;   
  nCnt number := 0;  
 begin  
  nTimeStart := dbms_utility.get_time;   
    for z in (select new_employee_id , salary, commission_pct   
                from emp_bulk_test   
                where department_id in   
                         (select department_id   
                         from departments d, locations loc   
                         where d.location_id = loc.location_id and country_id = 'US')   
         ) loop  
     nNewSalary := z.Salary + nvl(z.commission_pct, 0.01) * z.Salary;  
     update emp_bulk_test set New_Salary = nNewSalary where new_employee_id = z.new_employee_id;    
     commit;  
     nCnt := nCnt +1;  
    end loop;  
  nTimeEnd := dbms_utility.get_time;    
  DBMS_OUTPUT.PUT_LINE('Row by row 2: '||nCnt || ' - ' || trunc( (nTimeEnd - nTimeStart)/100,2) );   
 end;

Row by row 2:  340000 - 35,57

Prawie dwukrotnie szybciej. Jest się o co bić.

Na koniec przepiszmy procedurę na przetwarzanie masowe - BULK COLLECT + FORALL. Czy urwiemy jeszcze choć parę sekund?

Test 4:

 set serveroutpu on  
 declare  
  cursor cur is  
  select new_employee_id, salary, commission_pct from emp_bulk_test   
  where department_id in ( select department_id from departments d, locations loc where d.location_id = loc.location_id and country_id = 'US');  
  type cur_tab is table of cur%rowtype;  
  cur_t cur_tab;  
  type tabnum is table of number index by pls_integer;  
  tNewSalary tabnum;  
  nLimit integer := 1000;  
 ----------------------------------------  
  nTimeStart number := dbms_utility.get_time;   
  nTimeEnd number;   
  nCnt number := 0;  
 begin  
  nTimeStart := dbms_utility.get_time;   
   open cur;  
   loop  
    fetch cur bulk collect into cur_t  
    limit nlimit;  
     exit when cur_t.count = 0;  
     FOR i IN 1..cur_t.count LOOP  
      tNewSalary(i) := cur_t(i).Salary + nvl(cur_t(i).commission_pct, 0.01) * cur_t(i).Salary;  
     END LOOP;  
     forall i in 1..cur_t.count  
     update emp_bulk_test set New_Salary = tNewSalary(i) where new_employee_id = cur_t(i).new_employee_id;  
     commit;  
     nCnt := nCnt + cur_t.count;  
    END LOOP;  
  nTimeEnd := dbms_utility.get_time;    
  DBMS_OUTPUT.PUT_LINE('Bulk 2: '|| ncnt ||' - ' || trunc( (nTimeEnd - nTimeStart)/100,2) );   
 end;

 Bulk 2: 340000 - 2,91

Nie tylko parę sekund ale dwadzieścia razy szybciej niż w klasycznym przetwarzaniu wiersz po wierszu!

Podsumowanie czasów przetwarzań:

-----------------------------------------------

T1: Wiersz po wierszu: 55,42 sec

T2: Bulk wiersz po wierszy: 56,06 sec

T3: Wiersz po wierszu 2: 35,57 sec

T4: Bulk: 2,91 sec

Cóż, wyniki nie pozostawiają żadnych wątpliwości. Samo użycie BULK COLLECT bez procesu przygotowanego do przetwarzanie masowego nie przynosi korzyści. Za to poprawne użycie BULK COLLECT wraz z FORALL może przynieść znaczące korzyści wydajnościowe.

How to oracle

Szukaj na tym blogu

Szybciej nie będzie czyli jak (nie) używać bulk collect.

Komentarze

Prześlij komentarz