Aktualizacja tabeli, szybciej niż INSERT ... ON DUPLICATE KEY UPDATE Opcje

[BlackPig]

Witam, mam problem, który nie dał mi spać dzisiejszej nocy, dotyczący aktualizacji bazy danych.
Chodzi o sytuację, kiedy stale aktualizuję tabelę, która ma klucz glówny. Jak to przy aktualizacji,
mogą pojawiać się nowe dane, albo być poprawiane stare.
Dotąd robiłem to tak:
INSERT into tabela ON DUPLICATE KEY UPDATE....
Problem, polega na tym, że przy dużej liczbie danych zaczyna się zamulać...
Powodem może być specyika aktualizacji, w której orientacyjnie w 99% wypadków wykonywany jest UPDATE,
a INSERT bardzo rzadko.
Więc w praktyce moja metoda najpierw próbuje robić INSERT, który w 99% przypadków sie nie udaje
(okazuje się to po sprawdzeniu klucza głównego, w bardzo długiej liście), następnie wykonuje UPDATE, który sie udaje.

Moje pytanie, czy da się jakoś sprytnie odwrócić kolejność działań - coś w stylu:

UPDATE tabela (99% się udaje)
ON nie_było_co_updatować INSERT (wyjątek dla 1% wyjątków)

Zamiast:

INSERT INTO tabela (1% się udaje)
ON DUPLICATE KEY UPDATE (wyjątek dla 99%)

To jakiś tam pomysł, ale każda inna sugestia przyspieszająca aktualizację w takim wypadku byłaby mile widziana

[phpion]

Wrzuć sobie dane do tabeli tymczasowej o strukturze identycznej ze strukturą tabeli właściwej. Będziesz miał wówczas 2 tabele: 1 z aktualnymi danymi, 2 z danymi do zaimportowania. Aktualizację tabeli właściwej zrobisz bardzo szybko poprzez zapytania z wykorzystaniem EXISTS (aktualizacja) oraz NOT EXISTS (wstawienie). Coś w tym stylu:

[SQL] pobierz, plaintext 
INSERT INTO wlasciwa (pole) SELECT (pole) FROM tymczasowa WHERE NOT EXISTS (SELECT pole FROM wlasciwa WHERE id = tymczasowa.id)
[SQL] pobierz, plaintext 

Możliwe, że gdzieś się machnąłem w powyższym zapytaniu, ale ideę załapiesz.

[BlackPig]

Dzięki za pomoc
Rozumiem, że rozwiązanie bazuje na tym, że EXISTS/NOEXISTS ma działać znacznie szybciej niż DUPLICATE KEY - bo tutaj w sumie wykonujemy i wstawianie do tabeli tymczasowej (szybsze bo jest pusta), i potem jeszcze raz przekłądamy z tymczasowej do zwykłej hurtowo (ale na podobnej zasadzie)... też sprawdzajac powtórzenia...
Na razie udało mi się wyspać i uzyskać dobrą szybkość przez odśmiecenie projektu z kupy nąszalandzkich rozwiązań (złe przyzwyczajenia z małych CMSów), typu 5 razy za duże varchary, robienie pojedynczych query w każdej pętli Rozwiązania, więc narazie nie testuję - miałbym sporo do zmiany bo kilka tam tych tabel jest - zostawię to sobie jako ASA w rękawie na wypadek gdyby się znowu zmuliło
Ale jeszcze raz dzięki bo Twój post spowodował, że poczytałem o tabelach tymczasowych i memory z którymi nie miałem dotąd do czynienia, a dobrze o nich wiedzieć

[maly_swd]

a nie mozesz robic tak:
1. updatujesz
2. sprawdzasz mysql_affected_rows jesli jest 0 - to znaczy ze nic sie nie zupdatowalo i trzeba wykonac INSERTA

inne rozwiazanie to pobrac sobie do tablicy ID (to co masz kluczem glownym) i sprawdzac if($tablica[$klucz]) to znaczy ze trzeba update, else INSERT

[Mchl]

a nie mozesz robic tak:
1. updatujesz
2. sprawdzasz mysql_affected_rows jesli jest 0 - to znaczy ze nic sie nie zupdatowalo i trzeba wykonac INSERTA

Jak jednym zapytaniem wstawiamy jeden wiersz to może być. Gorzej jak wstawianych jest 500, z czego 250 na insert, 250 na update.

[erix]

Zawsze można spróbować napisać do tego procedurę.

[maly_swd]

Jak jednym zapytaniem wstawiamy jeden wiersz to może być. Gorzej jak wstawianych jest 500, z czego 250 na insert, 250 na update.

Kolega napisal: "Powodem może być specyika aktualizacji, w której orientacyjnie w 99% wypadków wykonywany jest UPDATE,
a INSERT bardzo rzadko.", Wiec przyjmujemy ze sa robione same UPDATY a INSERT to przypadek:)

Jest jeszcze: REPLACE INTO ... ale nie wiem jak z jego szybkoscia

Ten post edytował maly_swd 23.02.2010, 15:33:40

[Mchl]

Kolega napisal: "Powodem może być specyika aktualizacji, w której orientacyjnie w 99% wypadków wykonywany jest UPDATE,
a INSERT bardzo rzadko.", Wiec przyjmujemy ze sa robione same UPDATY a INSERT to przypadek:)

Tak napisał, ale nie wiemy, czy w jednym zapytaniu robi to dla wielu wierszy czy dla jednego. Zapewne dla jednego, ale pewności nie mamy.

Jest jeszcze: REPLACE INTO ... ale nie wiem jak z jego szybkoscia

Jeszcze gorzej: REPLACE = DELETE + INSERT dla wszystkich wierszy.

Jeżeli tabela jest InnoDB, a wykonywanych jest wiele zapytań INSERT/UPDATE jedno po drugim (np w pętli) to pomóc może wpakowanie całości do jednej transakcji i zatwierdzenie wszystkiego jednym COMMITEM.

[maly_swd]

Tak napisał, ale nie wiemy, czy w jednym zapytaniu robi to dla wielu wierszy czy dla jednego. Zapewne dla jednego, ale pewności nie mamy.

W sumie masz racje;)

Jeszcze gorzej: REPLACE = DELETE + INSERT dla wszystkich wierszy.

Kurcze, czlowiek uczy sie cale zycie:) - a mozesz mi podac linko do wyjasnienia tego:)
Czyli insert z on duplicate jest szybszy od replaca?

[Mchl]

http://dev.mysql.com/doc/refman/5.0/en/replace.html

Teoretycznie tak, bo przynajmniej część wierszy zostanie zaktualizowana prze UPDATE.

[BlackPig]

Widzę, że tutaj dyskusja rozgorzała Chociaż, że wszystko narazie działa dobrze po staremu to się włączę z powrotem:

1. updatujesz
2. sprawdzasz mysql_affected_rows jesli jest 0 - to znaczy ze nic sie nie zupdatowalo i trzeba wykonac INSERTA

nie wygląda mi to na dobre rozwiązanie nie dlatego, że nie wstawiam po jednym, ale z innego powodu:
mysql_affected_rows zwraca liczbę wierszy rzeczywiście zmienionych - ja pobieram dane cyklicznie z XML.
W prawdzie system z którego pobieram jest mądry i w jednym XML udostępnia mi tylko dane dotyczące tych meczów, w których coś się zmieniło, to niestety jeden XML zapisany jest w kilku tabelach, więc to że zmieniło się w XML nie znaczy, ze jest co updatować we wszystkich tabelach, na których zapytania i tak wykonam.... Może być więc tak, ze ktoś koryguje tylko literówkę w nazwisku strzelca bramki, a ja i tak muszę updatować całą strukturę... Wtedy podany sposób działa tak
1. update tabela z meczami
dane się nie zmieniły, więc mysql_affected_rows==0
w takim razie:
2. instert into tabela z meczami i mamy error duplicate key