import csv -> update other table

Door Dennis WhoCares op 16-06-2017 08:06 gewijzigd op 16-06-2017 10:03

7.708 views

Hi all,

ik ben weer eens bezig om mijn imports te versnellen, almede door advies om gehele csv bestanden te importeren naar een tijdelijke tabel.
Dit werkt idd een heeeel stuk sneller ;-)

Nou heb ik 1 tabel : dns_tickets
en nog een tabel : csvTickets

normaliter deed ik per regel uit de csv een insert of update, op basis van de key.
Dat begint na 10.000-20.000 regels beetje langzaam te gaan ;-)

Dus vervolgens doe ik nou :


...
$pdo = new PDO("mysql:host=$host;dbname=$database", 
        $username, $password,
        array(
            PDO::MYSQL_ATTR_LOCAL_INFILE => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        )
);
...
$stmt = $pdo->prepare("TRUNCATE csvTickets");
$stmt->execute();
$affectedRows = $pdo->exec("LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE csvTickets
      FIELDS TERMINATED BY ".$pdo->quote(';')."
      LINES TERMINATED BY ".$pdo->quote("\n"));
echo "Imported $affectedRows records";
$pdo->exec('DELETE FROM csvTickets WHERE TicketCode = "TicketCode"');

Dit werkt geweldig binnen een seconde is die al klaar met ALLE tickets te importeren.

Nou wou ik dus mijn dns_tickets tabel updaten:


UPDATE dns_tickets dns
INNER JOIN csvTickets csv
ON dns.ticket_code = csv.TicketCode
SET dns.ticket_watchers = csv.Watchers
WHERE dns.ticket_source = 2

Dit heb ik uitgevoerd in phpmyadmin om te kijken of het werkt.
Helaas loopt alles vast en is alles unresponsive.

Heeft iemand enig idee wat hier aan de hand is?
System time van Mysql sprong 90% en na 5 minuten gewacht te hebben heb ik via ssh de server een force reboot gegeven.

Ik hoopte dat ik zo nog sneller 20.000 records kon updaten van de al bestaande 170.000

Ivo P

16-06-2017 09:52

zit er een index op ticket_code en TicketCode?

---
Als je hele tabellen leeg gooit, is TRUNCATE mytable doorgaans sneller dan DELETE FROM mytable

TRUNCATE gooit de tabel weg en maakt hem opnieuw, leeg, aan. En hij hoeft dus niet stuk voor stuk de records te verwijderen en meta-data als autoincrement tellers, index etc bij te werken.

Dennis WhoCares

16-06-2017 10:02

Hi Ivo,

bedankt voor de tip om truncate te gebruiken.

Ehm, nee in principe zit er geen index op ticket_code en TicketCode
Ik kan csv.TicketCode wel een unique kunnen geven,
maar ticket_code zou heeeeel misschien niet uniek kunnen zijn.

Vergat trouwens de WHERE op te geven in m'n bericht.

Het kan zijn de ticket_code zelfde kan zijn, maar andere source.

Ivo P

16-06-2017 10:41

index is niet per se hetzelfde als unique.

een primary key dwingt uniek af, maar een key alleen zou dat niet doen.

ik begrijp uit het verhaal dat zowel TicketCode als ticket_code niet per se uniek voorkomen in de tabellen?

ik zou dan in elk geval een index zetten op die beide kolommen.
En eentje op ticket_source zou ook kunnen.

met die laatste zou het gemakkelijker moeten worden om uit de 170.000 records de betreffende te vinden.

En de key op de *code kolommen zou het gemakkelijker moeten maken om de beide tabellen tegen elkaar te leggen, zonder steeds 20.000*20.000 records te moeten langs lopen.

Dennis WhoCares

16-06-2017 11:05

Hi Ivo,
ik was net m'n comment aan het aanpassen :D

dns_tickets heeft wel een primary key op ticket_id
ik had daarnet csv.TicketCode een unique index gegeven en de update lijkt er per direct doorheen te gaan.

Is er nog een truukje om de eerst regel te laten vervallen ? zonder dat php de file hoeft te openen?
Of zou ik dat gewoon met shell_exec() moeten doen?

Ivo P

16-06-2017 11:09 gewijzigd op 16-06-2017 11:12

iets als

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

[size=xsmall]Toevoeging op 16/06/2017 11:11:34:[/size]

https://dev.mysql.com/doc/refman/5.7/en/load-data.html


LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

Dennis WhoCares

16-06-2017 12:10

Ivo, je bent een held!
Ik had idd de manual page eerst ff moeten kijken voordat ik ging vragen :)

Dennis WhoCares

18-06-2017 22:03

Dag allen,

Dit is alleen om m'n vraag uit te breiden en of ik dit wel goed doe, in de trend van ... performance.
Ik weet aan de hand van hierboven:
- hoeveel tickets ik geimporteerd heb
- hoeveel tickets ik geupdate heb
- hoeveel tickets er 'nieuw' zijn (welke nog niet in productie tabel aanwezig zijn)

Deze missende tickets wil ik dus toevoegen:


INSERT INTO csvInsertTest (ticket_code,ticket_summary,ticket_uniquecode,ticket_source) SELECT csv.TicketCode, csv.Summary,CONCAT('dimble_',csv.TicketCode) FROM csvImport csv WHERE NOT EXISTS (
    SELECT test.ticket_id FROM csvInsertTest test WHERE test.ticket_uniquecode = CONCAT('dimble_',csv.TicketCode)
)

Ben van Velzen

18-06-2017 22:23 gewijzigd op 18-06-2017 22:26

Voor een wat snellere select zou je dan een index op ticket_uniquecode willen zetten. Ook zou ik de NOT EXISTS omschrijven naar een LEFT JOIN met een controle op NULL. Dat werkt doorgaans een stuk sneller omdat de database dan de data gewoon aan elkaar kan ritsen in plaats van per record je hele table doorzoeken.

Dennis WhoCares

19-06-2017 09:12

Hi Ben,

zou je mij een voorbeeld kunnen geven hoe ik dat het beste kan toepassen ?
Het veld ticket_uniquecode is inderdaad Unique en een Index.

Ben van Velzen

19-06-2017 10:55

Zoiets?


INSERT INTO csvInsertTest (ticket_code,ticket_summary,ticket_uniquecode,ticket_source)
SELECT csv.TicketCode, csv.Summary, CONCAT('dimble_',csv.TicketCode) FROM csvImport csv
LEFT JOIN csvInsertTest test ON test.ticket_uniquecode = CONCAT('dimble_',csv.TicketCode)
WHERE test.ticket_uniquecode IS NULL

Reageren

Inloggen om te reageren