Szybki insert ponad 200 tys rekordów

Witam,
Mam do wstawienia ponad 200 tys. rekordów do tabeli. Baza danych jest na silniku PostgreSQL. Dane odczytywane są z pliku XML o strukturze:

    <row>
      <p1>02</p1>
      <p2>07</p2>
      <p3>01</p3>
    </row>
    <row>
      <p1>02</p1>
      <p2>07</p2>
      <p3>01</p3>
    </row>
.
.
.
    <row>
      <p1>02</p1>
      <p2>07</p2>
      <p3>01</p3>
    </row>

Wierszy (<row>) jest ponad 200 tys.
Odczytuję ten plik używając NativeXML i przelatując w pętli po każdym <row> tworzę z tych danych INSERT w formacie:

INSERT INTO tabela (p1,p2,p3) VALUES (:p1,:p2,:p3), (:p1,:p2,:p3), (:p1,:p2,:p3), (:p1,:p2,:p3) ..... (:p1,:p2,:p3);

W rezultacie mam utworzony tekst do wykonania takiego zapytania do bazy danych z np 1000 rekordów, który wykonuje przy użyciu komponentu TADQuery. Później dodaje kolejny 1000, następnie kolejny 1000 itd. aż dodam wszystkie 200 tys rekordów. Zajmuje to jednak sporo czasu. Około 15 minut. Czy jest jakiś lepszy/szybszy sposób aby wstawić tyle rekordów?

Czytałem o poleceniu COPY ale nie wiem jak można by użyć tego polecenia z plikiem XML

Hasło klucz Array DML https://docwiki.embarcadero.com/RADStudio/Sydney/en/Array_DML_(FireDAC)

FireDac - owszem pozwala na uruchomienie wielu pytań, jednak po stronie bazy wciąż po każdym zapytaniu uruchamiane są procesy związane z tym insertem t.j. triggery, budowanie kluczy itp... co przy dużej ilości rekordów wciąż może długo potrwać.

Zatem może to nie całkiem odpowiedź na pytanie ale może coś pomoże...
Z dużymi bazami mam doświadczenie jedynie w MYSQL i praktyka pokazuje, że przy setkach tysięcy czy milionach rekordów najlepiej sprawdza się "LOAD DATA INFILE".
Oczywiście to przypadek dość specyficzny bo musimy mieć dostęp co cmdline...
W pgSQL jest komenda "COPY"

Metoda ostatnio "masowo" wykorzystywana około 7 lat temu od prawie 15. Być może dziś coś uległo zmianie choć wątpię.

@PrzemysławWiśniewski:

Przetestowałem Array DML. Niestety tu w moim przypadku nie sprawdziło się. Czas wykonania niemal identyczny. Chyba problem leży w tym, że muszę wiersz po wierszu wyciągać dane z pliku XML i dlatego to tyle trwa.
Gdy wyrzuciłem z kodu wszelkie operacje łączenia tekstu i wykonywania zapytania do bazy i została sama pętla przelatująca po wierszach (<row>) z pliku XML to czas trwania jest niemal identyczny jak z dodawaniem rekordów do bazy. Zaobserwowałem, że do 50 tys rekordów jeszcze w miare szybko leci a później zaczyna zwalniać po każdym kolejnym 1000. Co może powodować takie zachowanie?

var node, nodeRow: TXMLNode;
     count, i: Integer;
     p1, p2: string;
     xml: TNativeXml;
begin
    xml.LoadFromFile('file.xml');
    node := xml.Root.FindNode('root');
    count := node.ContainerCount - 1;
  
      for i := 0 to count do
      begin
        nodeROW := node.Containers[i];
       
        p1 := nodeRow.Containers[0].Value;
        p2 := nodeRow.Containers[1].Value;

       lbl1.Caption := IntToStr(i);
     end;

end;

problem leży w tym, że muszę wiersz po wierszu wyciągać dane z pliku XML

Skoro spowalnia Cie odczyt z pliku przy każdym INSERT to może spróbuj najpierw jednorazowo wczytać cały plik do pamięci, a potem z niego dawać wrzutki do SQL?

@cerrato: wczytuje cały plik tutaj:

xml.LoadFromFile('file.xml');

i później z komponentu xml wyciągam dane.

Jak zauważyłeś, wąskim gardłem jest obsługa pliku xml w Delphi i wydaje mi się że masz rację. Swego czasu też robiłem próby na jakimś dużym pliku i szło to rzeczywiście bardzo wolno.
Jako że struktura tego pliku jest bardzo prosta to może potraktuj go jako zwykły plik tekstowy. Tutaj masz dużo możliwości przyspieszenia jego obsługi.
Najlepiej wczytaj go do jakiegoś streama i dopiero z niego po przeformatowaniu ładuj dane do bazy danych.

Sprawdz czy uzywasz recznych transakcji. Jak masz autocommit to bedziesz mial tragiczna wydajnosc zapisu.

Ile trwa odczyt a ile pojedynczy insert 1k rekordow?

a testowałeś tego mapera do obsługi xml https://getitnow.embarcadero.com/XMLMapper-27/

vpiotr napisał(a):

Sprawdz czy uzywasz recznych transakcji. Jak masz autocommit to bedziesz mial tragiczna wydajnosc zapisu.

Wydaje mi się, ze nie baza jest problemem, ale właśnie mielenie tego XML'a. Parę postów wyżej OP napisał Gdy wyrzuciłem z kodu wszelkie operacje łączenia tekstu i wykonywania zapytania do bazy i została sama pętla przelatująca po wierszach (<row>) z pliku XML to czas trwania jest niemal identyczny jak z dodawaniem rekordów do bazy. - czyli ja to rozumiem tak, że samo przemielenie XML'a bez zapisu do bazy trwało prawie tyle samo, co z zapisem. Raczej nie SQL jest tutaj wąskim gardłem.

Witam,
Rozwiązałem problem. Wyrzuciłem całkiem komponent TNativeXML i użyłem dobrej poczciwej TStringList'y. Trochę musiałem pokombinować z wyszukiwaniem właściwego fragmentu w tekście ale czas dodawania rekordów jest nieporównywalnie lepszy. Udało mi się zejść z 15 minut do 15 SEKUND! Tyle teraz wynosi wstawienie 285 tys rekordów.

Dzięki za wszelkie sugestie.
Pozdrawiam.

jm0x01 napisał(a):

@PrzemysławWiśniewski:

Przetestowałem Array DML. Niestety tu w moim przypadku nie sprawdziło się. Czas wykonania niemal identyczny.

ArrayDML dla PostgreSQL działa od wersji 8.1, dla niższych wersji jest emulowane (czyli insert po insercie) i nie będzie tu żadnego wzrostu wydajności.
Poza tym, jak chcesz mieć mega szybko po stronie bzy danych to warto wyłączyć na czas ładowania danych indeksy, triggery i klucze obce. A włączyć je po załadowaniu danych.
Oczywiście jeśli można.

Chyba problem leży w tym, że muszę wiersz po wierszu wyciągać dane z pliku XML i dlatego to tyle trwa.

Nie do końca; trwa tu ładowanie tego XMLa do pamięci i budowanie DOM.
Jeśli zależy Ci na naprawdę wydajnym przetwarzaniu XMLa, to musisz popatrzeć w stronę SAX.
Osobiście od dawna korzystam z OXML:
http://www.kluug.net/oxml.php
Kliknij w zakładkę performance na w/w stronie i sam zobacz.

Gdy wyrzuciłem z kodu wszelkie operacje łączenia tekstu i wykonywania zapytania do bazy

Stop, stop; jak łączysz te teksty?
Zresztą nieważne jak, ale jak nie używasz TStringBulder to zacznij go używać zamiast używać Pos, Copy, string + string, itp.

Jak wykonywanie zapytań - jakich zapytań?
To nie jest po prostu insert into tylko jakieś dodatkowe sprawdzenie danych w bazie przed zapisałem danych?

i została sama pętla przelatująca po wierszach (<row>) z pliku XML to czas trwania jest niemal identyczny jak z dodawaniem rekordów do bazy. Zaobserwowałem, że do 50 tys rekordów jeszcze w miare szybko leci a później zaczyna zwalniać po każdym kolejnym 1000.
Co może powodować takie zachowanie?

Transakcje bazodanowe.
Jak nimi zarządzasz?
Tak czy inaczej powinieneś robić to mniej więcej tak (dzielić na paczki, które będą objęte jedną transakcją):

1. BeginTransaction;
2. Dodawanie 1000 (tu trzeba potestować ile będzie najlepiej) wierszy.
3. CommitTransaction;
   I powtarzasz to dla wszystkich wierszy z XML...

var node, nodeRow: TXMLNode;
     count, i: Integer;
     p1, p2: string;
     xml: TNativeXml;
begin
    xml.LoadFromFile('file.xml');
    node := xml.Root.FindNode('root');
    count := node.ContainerCount - 1;
  
      for i := 0 to count do
      begin
        nodeROW := node.Containers[i];
       
        p1 := nodeRow.Containers[0].Value;
        p2 := nodeRow.Containers[1].Value;

       lbl1.Caption := IntToStr(i);
     end;

end;

Takie proste coś (zmiana tekstu w labelku) potrafi porządnie zwolnić, jeżeli to robisz przy każdej iteracji.

@wloochacz: Odnośnie zmiany tekstu na TLabel to u mnie nie ma żadnego wpływu na prędkość. Czas wykonania bez TLabel był taki sam. Chociaż przyznam, że ja używałem zmiany na TLabel co każde 1000 rekordów a nie co każdy pojedyńczy.

Odnośnie TStringBuilder to nie znam tego typu. Na razie wyczytałem tutaj: https://medium.com/@Zawuza/stringbuilder-vs-for-string-string-d1c82e14f990 że jest nieco wolniejszy od zwykłego string+string.

@wloochacz:

takie proste coś (zmiana tekstu w labelku) potrafi porządnie zwolnić, jeżeli to robisz przy każdej iteracji.

samo

lbl1.Caption := IntToStr(i);

bez przetworzenia kolejki komunikatów w niewielkim stopniu wpłynie na wydajność czasową procedury

Długotrwałe czynności warto wykonywać w pobocznym wątku — 15 sekund to wystarczająco długo, aby ten kod wydzielić z głównego wątku. Poza tym nie trzeba w każdej iteracji aktualizować wizualnego licznika, bo użytkownik i tak nie będzie w stanie odczytać jego wartości. Spokojnie można robić update interfejsu raz na sekundę.