[MYSQL] import van grote hoeveelheid data

[JPTrixx]

Ik heb 3 tabellen in mijn database:
categories:
-id
-name

products:
-id
-categories_id
-name

prod_details:
-id
-products_id
-size
-price

Ik heb dus een 15-tal categorien, een 4000-tal producten en per product ongeveer 5 groottes met bijhorende prijs wat maakt dat de laatste tabel ongeveer 20000 records heeft.

Om de zoveel tijd wordt via een formulier een excel file geupload met aangepaste prijzen van deze producten en/of met nieuwe producten. Ik moet dus alle records doorlopen, kijken of het product aanwezig is, indien ja dan moet ik de prijs updaten en indien neen dan moet ik het toevoegen. Hieronder vind je de query die ik heb gebruikt. Als ik die 20000 records moet doorlopen duurt dat echter heel lang. Wie kan me helpen om dit efficienter te laten verlopen?
Alvast enorm bedankt!!

for($i = 1; $i <= $aantalrecords; $i++){
$sql = mysql_query("SELECT d.id
FROM prod_details AS d
LEFT JOIN products AS p ON p.id = d.products_id
LEFT JOIN categories AS c ON c.id = p.categories_id
WHERE d.size = $size[$i]
AND p.name = $pname[$i]
AND c.name = $cname[$i]);
if(mysql_num_rows($sql) == 0){
//hier de code voor de INSERT
}
else
//hier de code voor de UPDATE
}

[Noork]

Kun je dit niet met een cronjob regelen? Dit in eventueel in stukken opdelen en dan bijhouden welke records al gedaan zijn.

[Firebirdy]

Iets dat je snel kan proberen: een index zetten op size, products.name en categories.name. Het zoeken zou stukken sneller moeten gaan.

[JPTrixx]

Noork schreef op dinsdag 10 maart 2009 @ 20:58:
Kun je dit niet met een cronjob regelen? Dit in eventueel in stukken opdelen en dan bijhouden welke records al gedaan zijn.

Bedankt voor je reactie.

Dat zou eventueel een mogelijkheid zijn maar ik zou toch liever in 1 keer alles doen met een efficientere query of zo.

[Noork]

Volgens mij zit je al snel aan de max execution time. Als je die niet kan wijzigen/verhogen, dan houdt het op.

[TeeDee]

Kan je niet het resultaat van een update niet bekijken?
Pseudo:

code:

update table
if rows affected ==0
 // nieuw product, insert

- Maak een tool die dit voor je regelt, en dit evt. via cron oid laten lopen (zoals Noork zegt).
- Is het Excel sheet al goed ivm de indeling? Zo nee, is hier verbetering in aan te brengen?
- 20K records in zijn totaliteit updaten hoeft niet lang te duren, maar met die joins mogelijk wel. Kan je daar nog verbetering in aanbrengen?
- Liggen de indexen ivm de SELECT query niet dwars?
- Als laatste: ik zie van jou nog niet echt richtingen. Alleen een dump hier je probleem en fix het ff.

[JPTrixx]

Firebirdy schreef op dinsdag 10 maart 2009 @ 21:10:
Iets dat je snel kan proberen: een index zetten op size, products.name en categories.name. Het zoeken zou stukken sneller moeten gaan.

Ik heb dit eens geprobeerd en dat loopt al een stuk sneller.
De excel file is gewoon een opsomming van 20000 records onder elkaar, aan die indeling kan ik helaas niets veranderen, die krijg ik zo aangeleverd.

[Bene]

Je mag zelf de primary key bepalen? Is er een reden waarom je geen REPLACE kan gebruiken?

Edit: Gaah, nm, ik ben stom

[ Voor 14% gewijzigd door Bene op 10-03-2009 21:43 ]

[TeeDee]

JPTrixx schreef op dinsdag 10 maart 2009 @ 21:29:
De excel file is gewoon een opsomming van 20000 records onder elkaar, aan die indeling kan ik helaas niets veranderen, die krijg ik zo aangeleverd.

'Mijn' stelregel is: shit in, shit out. Zorg dat je het goed aangeleverd krijg en verwerk het dan. Definieer, uit je startpost, eens "duurt dat echter heel lang".

[HuHu]

Firebirdy schreef op dinsdag 10 maart 2009 @ 21:10:
Iets dat je snel kan proberen: een index zetten op size, products.name en categories.name. Het zoeken zou stukken sneller moeten gaan.

Ik zou juist een INDEX zetten op alle id's. Dus op categories.id een PRIMARY KEY, op product.id ook een PRIMARY KEY, op categories_id een FOREIGN KEY of INDEX, op prod_details.id een PRIMARY KEY en op products_id een FOREIGN KEY of INDEX.

Op size, products.name en categories.name een INDEX zetten is ook wel handig, maar je hebt juist die id's nodig voor die JOINS.

Bene schreef op dinsdag 10 maart 2009 @ 21:43:
Je mag zelf de primary key bepalen? Is er een reden waarom je geen REPLACE kan gebruiken?

Edit: Gaah, nm, ik ben stom

Een INSERT INTO ... ON DUPLICATE UPDATE zou wel handig zijn denk ik zo. Dan bespaar je die SELECT query.

[MBV]

@TeeDee: soms kan je er echt niks aan veranderen. Je kan het soms makkelijker maken door het eerst ruw te verwerken in een perl-script o.i.d., maar in sommige gevallen (legacy systemen, gare workflow) is een excel-bestand de enige oplossing.
@JPTrix: indexen zijn hier de eenvoudigste oplossing voor. Je laat nu de database voor elk record in het bestand door een lijst van 40.000 items heen zoeken, vind je het gek dat het lang duurt? Met een index zal dat veel sneller gaan, daar zijn ze voor.
Andere oplossing, die om praktische redenen niet verstandig is (je ID's veranderen continue, dus google indexen kloppen na de volgende ronde niet meer), is je tabel leeggooien en opnieuw vullen. Dat kan alleen als je ID's in het excel-bestand staan.
Verder kan je unique constraints leggen op de kolommen die in de sheet 'primary key' zijn, en altijd REPLACE INTO ... gebruiken (zie de mysql spec).

offtopic:
code-tags maken je code een stuk leesbaarder, in dit geval dus [code=php]<?php $i = 1; ?>[/code]

[TeeDee]

MBV schreef op dinsdag 10 maart 2009 @ 22:17:
@TeeDee: soms kan je er echt niks aan veranderen. Je kan het soms makkelijker maken door het eerst ruw te verwerken in een perl-script o.i.d., maar in sommige gevallen (legacy systemen, gare workflow) is een excel-bestand de enige oplossing.

Volledig mee eens, maar ook Excel sheets kan je inlezen, aanpassen en als hapklare brokken aanleveren.

[Gomez12]

Gewoon set-based gaan werken.
Niet regel voor regel inlezen query uitvoeren en aan de hand daarvan een vervolg query uitvoeren, nu stuur je gewoon 40.000 losse query's op je dbase af dat kost gewoon tijd ( verbinding opbouwen etc )

Bouw programmeertechnisch gewoon 1 gigantische query met 20.000 WHERE ... OR ... regels erin ( Niet leuk voor het debuggen maar ok dan doe je het tijdens debuggen maar met sets van 100 regels of 1000 regels ) zodat je maar 1 keer die connectie hoeft op te bouwen en maar 1 query gebruikt om te bepalen wat er wel / niet inzit.
Daarna in je script taal over dat antwoord heen gaan loopen om te bepalen of het onderdeel moet worden van je update query of van je insert query. Dan gewoon die query's de hele tijd concatenaten zodat je daarna ook weer 2 gigantische query's hebt van bijv 18.000 regels updaten en 2.000 regels inserten.

Aan het einde deze 2 query's uitvoeren ( als je nog steeds in tijdsproblemen komt kan je voordat je deze 2 query's uitvoert je indexen uitzetten en na deze 2 query's je indexen weer aanzetten, maar je mag nu niet echt meer in tijdsnood komen, 20.000 regels toevoegen in 1 query is een peuleschil voor een normale dbase )

Dan zit je inplaats van met 40.000 query opeens nog maar met 3 query's, ik gok dat je hele probleem weg is. Je doet nu 20.000 zoekacties op 20.000 artikelen, dat duurt per stuk niet lang, maar 20.000x kort is ook lang

Simpele bij vraag, wat doe je met de overgebleven artikelen die dit keer niet in het bestand staan, maar bij volgende aanlevering wel weer?

[ Voor 3% gewijzigd door Gomez12 op 10-03-2009 22:23 ]

[MBV]

Gomez12 schreef op dinsdag 10 maart 2009 @ 22:21:

Bouw programmeertechnisch gewoon 1 gigantische query met 20.000 WHERE ... OR ... regels erin ( Niet leuk voor het debuggen maar ok dan doe je het tijdens debuggen maar met sets van 100 regels of 1000 regels ) zodat je maar 1 keer die connectie hoeft op te bouwen en maar 1 query gebruikt om te bepalen wat er wel / niet inzit.

Waarom zou je dat doen, als je met een REPLACE INTO al het probleem kan oplossen? Anders zal je alsnog 100.000 inserts/replaces moeten doen. Los daarvan is jouw oplossing echt een waardeloze oplossing: je komt al heel snel in de knoei met te lange query-groottes, te lange verwerkingstijden van MySQL, en een draak van een query om te onderhouden.

ALS je ongeveer op deze weg het probleem wilt oplossen, maak dan een 'hash' (gewoon concat is waarschijnlijk al goed genoeg) van id, pname en cname uit de tabel, dump die in een array, sorteer hem. Dat kan allemaal in 1 query, bliksemsnel.
Dump ook een hash van de waarden uit het excel-bestand in een andere array, sorteer die op dezelfde manier, en loop er overheen om te kijken wat er toegevoegd moet worden, en wat alleen replaced. Dat kan in O(n1+n2) tijd, en het is ook kinderlijk eenvoudig om te zien wat er verwijderd moet worden. Zie Wikipedia: Merge algorithm voor het algoritme om te kijken wat er toegevoegd, en wat verwijderd moet worden.

---

@TS: heb je eraan gedacht dat je misschien gegevens moet verwijderen? Daarvoor zou je bovenstaande methode goed kunnen gebruiken.

[ Voor 6% gewijzigd door MBV op 10-03-2009 23:34 ]

[GlowMouse]

Je hebt de volgende indices nodig voor optimale snelheid, twee veldnamen tussen haakjes betekent dat die in dezelfde index moeten zitten:

code:

details: (size)
products: (id,name)
categories: (name,id)

En dan gewoon doen zoals in de OP, die code moet binnen een minuut draaien en waarschijnlijk sneller.

Let ook op dat je de velden niet langer maakt dan noodzakelijk (smallint of anders mediumint voldoende? varchar(255) echt nodig?), dat scheelt aanzienlijk in je indexgrootte.

[ Voor 21% gewijzigd door GlowMouse op 10-03-2009 23:48 ]

[Motrax]

Waar laat je die Excel file? Laad je die in een staging tabel(len) en vergelijk je daarna met je normale tabellen?

Even ranzig opzetje:

SQL:

UPDATE NORMAAL
WHERE (SELECT FROM STAGING, NORMAAL
WHERE STAGING.PK = NORMAAL.PK
AND STAGING.PRIJS <> NORMAAL.PRIJS);

INSERT NORMAAL
WHERE (SELECT FROM STAGING, NORMAAL
WHERE STAGING.PK <> NORMAAL.PK);

REPLACE INTO is leuk, maar is wel weer een extra delete op de db. Aan de andere kant, daarmee voorkom je dat je twee queries hebt, één voor insert en één voor update. Het ligt er aan wat de verhouding is tussen nieuwe, niet gewijzigde en gewijzigde records.

Om heel eerlijk te zijn zie ik het probleem niet. 20.000 records moet een makkie zijn, tenzij je op heel trage hardware zit. En met traag bedoel ik een Pentium III met 256 MB intern...

[MBV]

20.000 table-scans door 2 tabellen van 4000 en 20.000 regels lang, 2x string comparison, en gewoon al 20.000 losse queries zijn nou niet bepaald goed voor de performance... Met indexen zal het al een stuk beter gaan

Die laatste query zal trouwens altijd heel veel inserts doen Dit lijkt mij een betere:

SQL:

INSERT normaal (pk, gegevens)
SELECT pk, gegevens
FROM staging
LEFT JOIN normaal 
  ON staging.pk = normaal.pk
WHERE normaal.pk IS NULL

Diezelfde truc zal je ook bij de update kunnen toepassen

Op zich geen slecht idee trouwens om het zo op te lossen, als je toch alle records gaat replacen, kan je ze net zo snel allemaal importeren in een aparte tabel

[ Voor 3% gewijzigd door MBV op 11-03-2009 00:16 ]

[Motrax]

Jouw query zal ook dezelfde hoeveelheid inserts doen

Maar... jouw query is wel sneller.

Overigens is dit standaard ETL aanpak. Laden in staging en daarna pas in de targettabel gooien is niet bepaald nieuwe techniek.

[Verwijderd]

Ik krijg de impressie dat het excel bestand leidend is voor alle producten die aangeboden moeten worden.
Is het ook zo dat producten die al wel aanwezig waren in de db, maar niet meer in de excel, verwijderd mogen worden?
Want dan kan je de tabel legen en simpel alle producten van de excel opnieuw toevoegen.

[MBV]

Verwijderd schreef op woensdag 11 maart 2009 @ 10:34:
Ik krijg de impressie dat het excel bestand leidend is voor alle producten die aangeboden moeten worden.
Is het ook zo dat producten die al wel aanwezig waren in de db, maar niet meer in de excel, verwijderd mogen worden?
Want dan kan je de tabel legen en simpel alle producten van de excel opnieuw toevoegen.

En dan veranderen je ID's dus met elke import, lijkt me geen optie. Denk aan de google hit op www.example.com/products/42

Motrax schreef op woensdag 11 maart 2009 @ 10:17:
Jouw query zal ook dezelfde hoeveelheid inserts doen

Die van mij voert maximaal COUNT(staging) inserts uit, die van jouw (COUNT(staging)-1) * (COUNT(normaal)-1). Je geeft een cartesisch product, doordat je alleen joint op normaal.pk <> staging.pk.

Overigens is dit standaard ETL aanpak. Laden in staging en daarna pas in de targettabel gooien is niet bepaald nieuwe techniek.

offtopic:
Uiteraard, maar ik heb het als PHP-prutzor bij mijn vorige baas nog nooit zo gedaan. Bewust of onbewust neem je toch de slechte gewoontes van je voorganger over Batchjobs bouwden meestal een hele lange insert-query op, daarna TRUNCATE TABLE x, daarna INSERT x..., altijd een moment dat de database leeg was
Nee, we gaan niet over op postgre of zelfs InnoDB, want je weet maar nooit wat er dan kapot gaat

[Verwijderd]

MBV schreef op woensdag 11 maart 2009 @ 11:10:
[...]

En dan veranderen je ID's dus met elke import, lijkt me geen optie. Denk aan de google hit op www.example.com/products/42

Dat is een aanname die we beiden maken, weliswaar op verschillende manieren.
Als je producten aangeleverd krijgt in een sheet dan kan hier de product id in staan. Indien je deze product code gebruikt op de te publiceren locatie dan blijven ze voor google gelijk. Is het ID gebaseerd op de auto increment dan klopt jou aanname en is mijne inderdaad geen nette oplossing.

[cyberstalker]

Welke versie van MySQL gebruik je trouwens? Vanaf versie 5.1 ondersteunt MySQL namelijk standaard de CSV engine. Als je dan je excel exporteert naar CSV en op de juiste plek neerzet is deze direct in je database beschikbaar. Vervolgens zet je de gegevens met een simpele query over, dit hoeft dan ook geen grote query te zijn .

[MBV]

Verwijderd schreef op woensdag 11 maart 2009 @ 11:22:
[...]

Dat is een aanname die we beiden maken, weliswaar op verschillende manieren.
Als je producten aangeleverd krijgt in een sheet dan kan hier de product id in staan. Indien je deze product code gebruikt op de te publiceren locatie dan blijven ze voor google gelijk. Is het ID gebaseerd op de auto increment dan klopt jou aanname en is mijne inderdaad geen nette oplossing.

Ik baseer de aanname op de start-post:

SQL:

WHERE d.size = $size[$i]
AND p.name = $pname[$i]
AND c.name = $cname[$i]

Als hij ID's heeft, zou het wel heel dom zijn wanneer hij strings gaat vergelijken

[Verwijderd]

Het maakt waarschijnlijk ook nog uit als je bij het begin van het inlezen van de excel sheet een transactie start op de database, en die transactie commit als je aan het eind bent van de excel sheet.

[MBV]

Dat is vooral van belang bij de drop-table aanpak Ik dacht dat transacties juist voor meer overhead zorgden. Daarnaast is InnoDB natuurlijk een stuk trager dan MyISAM.

[ Voor 53% gewijzigd door MBV op 11-03-2009 22:24 ]

[Borizz]

MBV schreef op woensdag 11 maart 2009 @ 22:23:
Dat is vooral van belang bij de drop-table aanpak Ik dacht dat transacties juist voor meer overhead zorgden. Daarnaast is InnoDB natuurlijk een stuk trager dan MyISAM.

Een beetje offtopic, maar waar haal je vandaan dat InnoDB trager is dan MyISAM? Check: http://www.mysqlperforman...falcon-benchmarks-part-1/ .

[MBV]

offtopic:
Goh, performance was toch een van de redenen dat ze niet over wilden. Oh ja, en "Stel je voor dat het daardoor stuk gaat", wat gezien de architectuur niet zo'n raar idee was.
MSSQL => drop-table gevolgd door insert complete tabel => MySQL server lokaal => replicatie => MySQL server remote. Mag je raden wat MyISAM doet als MSSQL een rollback geeft...