Ik maak een tijdserie tabel (datetime) met daarin de uurwaarden vanaf 2008 tot en met 2015. Momenteel maak ik die aan met een query die slechts een datumtijd waarde per keer toevoegd.
DATE_ADD("2008-01-01 00:00:00" + INTERVAL 1 HOUR) Van 2008 naar 2015 voeg ik dus 70.128 queries uit. Dat moet efficienter en dus sneller kunnen, maar hoe?
Door het niet te doen. Tenzij je referentiële integriteit belangrijk vind en je op een shared hosting zit waar triggers niet ondersteund worden is het niet nodig. Dus laten we het probleem bij de wortel aanpakken. Wat is het probleem dat je wil oplossen?
Ik wil historische verbruiksdata doortrekken naar toekomstige jaren. Maw uurwaarden van 2008 wil ik doortrekken naar 2009 en verder, maar met behoud van uurverbruik per weekdag per maand.
70.128 queries of inserts? Ik vrees dat dat niet sneller kan, je maakt een record aan met uurwaarde en verbruiksgegevens. Is toch snel gevuld met een scriptje? Gewoon doen dus. Kan je OS niveau naar MySQL of moet je alles met php(admin) oid doen?
@The Force: wat bedoel je met referentiële integriteit? TS heeft nergens gemeld dat er meer tabellen zijn en dat er referenties zijn. En wat moet je met een trigger? Die weet de uurwaarde echt niet vanzelf.
"uurverbruik per weekdag per maand", dat klinkt niet logisch. In een database sla je normaal gezien nooit de 'per' op. Wat je precies bedoelt met "maar met behoud van" snap ik dan niet. Het blijft houdbaar totdat je data gaat verwijderen. In de database sla je bijvoorbeeld op wat het verbruik was in een bepaald uur. Dan kan je vervolgens met queries berekeningen doen en er relevante data uit halen. Als ik het goed begrijp (correct me if I'm wrong), wil je alvast elk uur aan de database gaan toevoegen en daar dan later data bij op gaan slaan? Zoals wat het verbruik dat uur was? Daar zou je inderdaad berekeningen op los kunnen laten om te extraheren naar de toekomst. Het lijkt me echter logischer als je alleen de uren opslaat waar je ook data van hebt. Scheelt je gigantisch veel inserts en je hebt geen nutteloze data in je database. Maar met de beperkte uitleg blijft het giswerk.
Mocht je het toch willen doen dan raad ik je de MySQL multiple insert syntax aan.
Code (php)
1
INSERT INTO MijnTabel (kolom1, kolom2) VALUES ('Waarde Rij 1', 1), ('Waarde Rij 2', 2), (...)
Je hele query wordt dan als één batch uitgevoerd, wat vele malen sneller kan zijn. Check
http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html voor alles over insert optimalisatie.
Quote:@The Force: wat bedoel je met referentiële integriteit? TS heeft nergens gemeld dat er meer tabellen zijn en dat er referenties zijn. En wat moet je met een trigger? Die weet de uurwaarde echt niet vanzelf.
De TS had helemaal niks over de situatie uitgelegd, dus dat hij niet zegt dat er meer tabellen zijn hoeft nog niet te betekenen dat die er ook niet zijn. Gezien het gebrek aan informatie heb ik een poging gedaan in te schatten waarvoor de TS die lijst met uren zou gebruiken. Aannemelijk was dat hij die zou gebruiken om ergens anders te controleren dat een veld een waarde in de datetime had zitten die tussen 2008 en 2015 zit en een heel uur is. In dat geval zou hij dat beter met een trigger die constraint af kunnen dwingen.
Zoals ik het nu begrijp is het het snelst te realiseren door de tijdserie in php te schrijven (bijvoorbeeld in een array) en die vervolgens met een implode in te voegen in de database mbv een multiple insert. Ik ga ermee aan de slag. Dank voor jullie hulp!
@The Force OK, Mijn opmerking is wellicht vanuit mijn voorkennis, ik werk ook met time series en dan niet met uurwaarden maar zelfs met kwartierwaarden. Bijvoorbeeld op 2008-01-01 00:00:00 werd 15kwH gebruikt. Het zijn dus (historische) gegevenstabellen waarbij een sleutel id_meter is opgenomen. In onze databases (oracle) is het zinloos om de tijdwaarde uit de kwartierwaarden tabel verder uit te normaliseren. Een date attribuut in oracle is efficient klein en dekt yymmddhhmiss.
Jochem vlokstra op 20/10/2011 22:38:03:Ik wil historische verbruiksdata doortrekken naar toekomstige jaren. Maw uurwaarden van 2008 wil ik doortrekken naar 2009 en verder, maar met behoud van uurverbruik per weekdag per maand.
Maar waarom wil je die dummydata (want dat is het) gaan opslaan? Je weet dat iemand in 2008 op uur X een verbruik van Y had, dit heeft geen enkele relatie met zijn mogelijke verbruik in 2015. Dit mogelijke verbruik in 2015 kun je iedere keer opnieuw uit je duim zuigen, je hebt de data uit 2008 nog steeds in de database staan.
INSERT INTO tabel
SELECT datumveld + INTERVAL 1 YEAR FROM andere_tabel WHERE year(datumveld) = 2008
UNION
SELECT datumveld + INTERVAL 1 YEAR FROM andere_tabel WHERE year(datumveld) = 2009
UNION
SELECT datumveld + INTERVAL 1 YEAR FROM andere_tabel WHERE year(datumveld) = 2010;
En als je een InnoDB-engine gebruikt kun je transacties gebruiken, dit levert een aanzienlijke snelheidswinst op.
Daarbij is een eenmalige invoer van 70.000 rijen echt niet zwaar.
