Neil deGrasse Tyson to mój ulubiony astrofizyk

Jest na tyle dobrym mówcą, że kiedy opowiada o fizyce, to mam wrażenie, że ją rozumiem.


  • W punkcie 1 zapiszemy do pliku jego treść jego tweeta
  • W punkcie 2 - tabelę z milionem wierszy
  • W punkcie 3 - tą samą tabelę, tyle że szybciej (porównanie w punkcie 5)
  • W punkcie 4 - krótko o buforze

Let’s get to business

1. Jak zapisać dane do pliku

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DECLARE
   l_directory_name   all_directories.directory_name%TYPE;
   l_open_mode        CHAR(1) DEFAULT 'w';
   l_file_name        VARCHAR2(100);
   l_file             utl_file.file_type;
   l_file_input       VARCHAR2(1000);
BEGIN
   l_directory_name   := 'UTL_FILE_DIR';
   l_file_name        := 'tyson_quote.txt';
   l_file_input       := 'According to the song, Rudolph’s nose is shiny, which means it reflects rather than emits light. Useless for navigating fog.';
   l_file             := utl_file.fopen(location => l_directory_name
                                       ,filename => l_file_name
                                       ,open_mode => l_open_mode);
   utl_file.put_line(l_file,l_file_input);
   utl_file.fclose(l_file);
END;
/
Wiersz Opis
3 W trybie write (l_open_mode := 'w'), zawartość pliku zostanie wyzerowana przy każdym kolejnym jego otwarciu.
W trybie append (l_open_mode := 'a'), po 3 wykonaniach procedury miałbym 3 dodane kolejno pod sobą wiersze.
15 Mógłbym tu też użyć utl_file.fclose_all.

2. Jak zapisać dane do pliku - tyle że więcej

Najpierw więcej danych. Tworzę tabelę z milionem wierszy.

1
2
3
4
5
6
7
8
9
10
11
DROP TABLE blurred_lines;
/
CREATE TABLE blurred_lines(
   id NUMBER(*,0)
);
/
   INSERT INTO blurred_lines
      SELECT level
        FROM dual CONNECT BY
         level <= 1000000;
/

A teraz właściwa procedura

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DECLARE
   l_directory_name    all_directories.directory_name%TYPE;
   l_open_mode         CHAR(1)DEFAULT 'w';
   l_file_name         VARCHAR2(100);
   l_file              utl_file.file_type;
   l_file_input        VARCHAR2(1000);
   l_timestamp_start   TIMESTAMP;
BEGIN
   l_timestamp_start   := systimestamp;
   l_directory_name    := 'UTL_FILE_DIR';
   l_file_name         := 'blurred_lines.txt';
   l_file              := utl_file.fopen(location  => l_directory_name
                                        ,filename  => l_file_name
                                        ,open_mode => l_open_mode);
   FOR i IN (SELECT *
               FROM blurred_lines) 
   LOOP 
      l_file_input := i.id;
      utl_file.put_line(l_file,l_file_input);
   END LOOP;
   utl_file.fclose(l_file);
   DBMS_OUTPUT.PUT_LINE(systimestamp - l_timestamp_start);
END;
/ 
Wiersz Opis
15-20 Wiersz po wierszu, zapisuję dane do pliku. Tryb write (l_open_mode := 'w') może zostać. Plik nie zostanie nadpisany, dopóki go nie zamknę i ponownie otworzę.
22 Odczytuję, ile czasu zajęło wykonanie programu.
Wynik i porównanie z kolejnym sposobem w punkcie 5

3. Jak zapisać dane do pliku - tyle że więcej i szybciej

Pętla powyżej wykonała 1kk iteracji - za każdym razem dodając nową linię w pliku utl_file.put_line(l_file,l_file_buffer);
A co, gdyby zapisywać więcej danych na raz?

Dane zostają te same. Kod przepisuję tak, aby pod zmienną zapisywaną przez w pliku przez put_line było więcej danych (w tym konkretnym przypadku - liczb).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
DECLARE
   l_directory_name    all_directories.directory_name%TYPE;
   l_open_mode         CHAR(1)DEFAULT 'w';
   l_file_name         VARCHAR2(100);
   l_file              utl_file.file_type;
   l_file_buffer       VARCHAR2(4000);
   l_buffer_size       PLS_INTEGER := 500;
   l_i                 PLS_INTEGER := 0;
   l_line_separator    VARCHAR2(10):= chr(10);
   l_timestamp_start   TIMESTAMP;
BEGIN
   l_timestamp_start   := systimestamp;
   l_directory_name    := 'UTL_FILE_DIR';
   l_file_name         := 'blurred_lines_without_input.txt';
   l_file              := utl_file.fopen(location  => l_directory_name
                                        ,filename  => l_file_name
                                        ,open_mode => l_open_mode);
   FOR i IN(SELECT *
              FROM blurred_lines)
   LOOP 
      l_i            := l_i + 1;
      IF l_i < l_buffer_size 
      THEN
         l_file_buffer := l_file_buffer || i.id
                          || l_line_separator
                          ;
      ELSE
         utl_file.put_line(l_file,l_file_buffer || i.id);
         l_i             := 0;
         l_file_buffer   := NULL;
      END IF;
   END LOOP;
   utl_file.put_line(l_file,l_file_buffer);
   utl_file.fclose(l_file);
   DBMS_OUTPUT.PUT_LINE(systimestamp - l_timestamp_start);
END;
/
Wiersz Opis
14-19 Zamiast wykonywać utl_file.put_line z każdą iteracją, tworzę bufor, w którym przechowam maksymalnie l_buffer_size wartości.
7-8 Wartość l_buffer_size musi być dobrana tak, żeby nie przekroczyć pojemności l_file_buffer (w tym przypadku 4kB).
Szerszy opis pod tabelą.
35 Odczytuję, ile czasu zajęło wykonanie programu.
Wynik i porównanie z poprzednim sposobem w punkcie 5

4. Rozmiar bufora

Każda wprowadzana liczba (i.id) zajmuje n bajtów, gdzie n to liczba cyfr, z której dana liczba się składa.
W skrajnym przypadku, program będzie przekazywał do bufora (l_file_buffer) 499 liczb z 6 cyframi i 1 liczbę z 7 cyframi.
Do każdej liczby dodajemy też 1 bajt za l_line_separator := chr(10).
Ostatecznie, aby mieć pewność że procedura się wykona, nasz bufor musi mieć minimum 499*(6+1) + 1*(7+1) = 3501 bajtów.

Za mały

utl_file_write_050.png

Przekazuję do bufora 2 wartości z tabeli blurred_lines (1000000, 999999) wraz z l_line_separator := chr(10) po każdej z nich.
Razem potrzebuję 1*(6+1) + 1*(7+1) = 15 bajtów, podczas gdy l_buffer_size jest zdeklarowany na max 14 bajtów.
Baza zwraca błąd.

Idealny

utl_file_write_055.png

Tym razem zdeklarowałem bufor idealnie na taką wartość (15B), jaka zostanie wprowadzona. Transakcja wykonana, plik poprawnie zapisany.

5. Bez bufora czy z buforem - porównanie

Bez owijania w bawełnę:

Bez bufora

utl_file_write_060.png

Z buforem

utl_file_write_065.png

Zaoszczędziliśmy 8 sekund!!!
Co w tym czasie można zrobić, pozostawiam wyobraźni developerów.


…A tak poważnie: skróciliśmy wykonywanie danego zadania o ponad 57%. W przypadku innego zestawu danych, można się spodziewać jeszcze lepszych rezultatów.

Kolejny, podobny w wykonaniu sposób na tak poważny skok wydajnościowy to usunięcie non-query DML (INSERT|UPDATE|DELETE) z loopów.

Źródła:

  1. https://docs.oracle.com/cd/B28359_01/appdev.1…
  2. https://docs.oracle.com/cd/B19306_01/appdev.1…
  3. https://connor-mcdonald.com/2018/06/18/juicin…
  4. https://en.wikipedia.org/wiki/Kilo-