optimalizacia rychlosti vkladania udajov

fliper333 · před 8 lety

Ahojte,

Potreboval by som poradit:

Mam databazu udajov, ktoru potrebujem kazdu hodinu aktualizovat. Kazdu hodinu nacitam nove udaje z internetu (vo formate JSON, ktory pomocou funkcie json_decode prevediem na array) a potom toto pole porovnavam s databazou. Polozky ktore su v databazy a nie su v novom JSONe, oznacim ze su stare, polozky ktore su v databazy a ktore su aj v JSON ignorujem a polozky ktore su v JSON a nie su v databazy, do databazy vlozim.

Robim to cez asi takto:

foreach
{
...
$values['active'] = 2;
dibi::query('INSERT INTO [data] ', $values, ' ON DUPLICATE KEY UPDATE [active] = 2'); // nove polozky a existujuce polozky ktore su stale aktivne oznacim stavom 2
}
dibi::query('UPDATE [data] SET [active] = 0 WHERE [active] != 2'); // vsetky ostatne polozky (0 - boli stare este pred update, 1 - su stare po tomto update) oznacim stavom 0
dibi::query('UPDATE [data] SET [active] = 1 WHERE [active] = 2'); // novym polozkam nastavim stav 1

Problem je ten, ze v tom JSON subore je cca 50.000 poloziek a cely skript trva velmi dlho (cca pol hodinu). Rozmyslal som ci neexistuje moznost skombinovat to do vacsieho query, ci by to nebolo rychlejsie ako 50.000 malych query, ale neviem ako na to.

Vopred vdaka za radu.

Milo · před 8 lety

Záleží, kolik řádků tabulka obsahuje a kolik záznamů je nových. Pokud je nových jen pár, asi se vyplatí nejprve SELECT existujících a ověření duplicity v PHP.

$data = json_decode(...);
$oldIds = dibi::query('SELECT id FROM data')->fetchPairs('id', 'id');
$updateIds = array();

foreach ($data AS $values) {
    if (isset($oldIds[$values['id']])) {
        $updateIds[] = $values['id'];
    }
    else {
        $values['active'] = 2;
        dibi::query('INSERT INTO data %v', $values);
    }
}

if (count($updateIds) > 0) {
    // Tady to bude chtit rozdelit na vic query, podle velikosti pole IN, napr. po stovce
    dibi::query('UPDATE data SET active = 2 WHERE id IN %in', $updateIds);
}

dibi::query('UPDATE data SET active = 0 WHERE active != 2');
dibi::query('UPDATE data SET active = 1 WHERE active = 2');

Další možností optimalizace je použít multiinsert, jak u tvého řešení, tak mého. Pro modifikátor %m to ale chce přetransformovat pole $data.

dibi::query('INSERT INTO data %m', $data2);

Milo · před 8 lety

Čas by mohlo ušetřit i prepared statements MySQLi::Prepare() ale to je jen teorie…

HosipLan · před 8 lety

Znáš transakce? Celé to do jedné obal. Zrychlíš to o několik řádů.

dibi::begin();
// tvuj script
dibi::commit(); // když tohle nezavoláš, změny se vrátí. Tak bacha!

fliper333 · před 8 lety

HosipLan – Parada, presne taketo nieco som potreboval. Transakcie poznam, ale nevedel som ze to ovplyvnuje rychlost, myslel ze sa to pouziva skor na zabezpecenie proti tomu aby si do databazy nevlozil len polovicu dat ak sa v druhej vyskytuje chyba. Vysledok – z 30 minut na 30 sekund. Diky

Milo – vdaka za napad, v noci som skusal nieco podobne, ale potom som nasiel este lepsi sposob ako na to

foreach
{
$active[] = $values['id']; // ulozim si idcka aktivnych poloziek a na konci nastavim stav hromadne cez 2 query
dibi::query('INSERT IGNORE INTO [data] ', $values); // ignorujem tie polozky ktore tam uz su
}
dibi::query('UPDATE [data] SET [active] = 0 WHERE [id] NOT IN %in', $active);
dibi::query('UPDATE [data] SET [active] = 1 WHERE [id] IN %in', $active);

Pretoze data sa menia len malo (kazdu hodinu cca 10% novych), tak toto v kombinacii s HospiLanovym riesenim zaberie len par sekund.

Vdaka este raz za rady obom.

Editoval fliper333 (9. 10. 2011 13:40)

Oznámení

optimalizacia rychlosti vkladania udajov

před 8 lety

před 8 lety

před 8 lety

před 8 lety

před 8 lety