[PHP / PervasiveSQL] 200.000+ records updaten in één script

[Crazybyte]

Bij het bedrijf waar ik voor werk heeft men besloten om hun numerieke artikelnummers te verlengen van 9 naar 10 posities.

In het verleden heeft men de nummers hier ook al eens verlengd van 7 naar 9 posities, maar destijds werd er gewoon '00' als suffix toegevoegd. Aangezien dit echter niet via het ERP-pakket zelf kan, heeft de persoon die dat destijds gedaan heeft, het rechtstreeks via SQL gedaan met de volgende query.

SQL:

UPDATE "ARTIKEL" SET artikelnummer = CONCAT(left(artikelnummer,7), '00')
 WHERE artikelnummer > '0000000' and artikelnummer < '9999999'
and length(artikelnummer) = 7

Echter nu komt er niet gewoon een '0' voor of achter maar ertussen. Zover ik nu heb uitgeprobeerd lukt het niet om CONCAT(CONCAT(left(artikelnummer,7), '0'), right(artikelnummer,2)) of iets dergelijks te doen.

Ik ben dus aan de slag gegaan om het dan maar via PHP te doen en dat gaat op zich prima, maar ik moet tussen de 150.000 en 250.000 records updaten. Met max_execution_time op 30 seconden kom je er dan niet. Dat is echter wel te voorkomen door die tijdelijk met set_time_limit gewoon te verhogen naar 22800 seconden (), dit aangezien het updaten van 4000 regels al zo'n 10 minuten in beslag neemt.

Echt helemaal happy met die oplossing ben ik natuurlijk niet, maar als het niet anders kan, dan zal het moeten. Tevens wil ik eigenlijk ook wel kunnen zien waar het script ongeveer aan bezig is, door bijvoorbeeld per tabel aan te geven dat hij daaraan begonnen is en weer klaar is.

Mijn vragen aan jullie is nu:
- Kan dit anders, sneller, handiger? (Het hoeft niet perse met PHP)
- Hoe kan ik ervoor zorgen dat ik tijdens het uitvoeren van het script toch informatie op mijn scherm te zien krijg, zodat ik zie dat bepaalde delen al gedaan zijn, i.p.v. enkel achteraf?

[B-Man]

4000 regels in 10 minuten? Dat is wel heel erg lang. Laat anders eens wat code zien, want ofwel je database is verschrikkelijk druk/traag, of je code is inefficient.

Overigens is het juist handig om dit wel met een SQL query te doen, aangezien dat je 250.000 records naar PHP kopieren scheelt. De database kan dat alles zelf in-memory doen.
Plaats anders meteen ook de SQL query die je geprobeerd hebt, en geef aan wat niet werkt.

-- edit: mocht je dit toch per se met PHP willen doen, dan kan ik je wel vertellen hoe je gedurende de uitvoer van het script info op je scherm kunt krijgen.
- Wil je uitvoer in je browser (niet aan te raden met dit soort langlopende scripts, die kun je beter via de command line aanroepen), dan zul je eerst een berg spaties naar de client moeten sturen, een flush(), dan je melding, flush(), melding, flush(), enz.
- Of het script dus aanroepen vanaf de commandline op je server/pc, en de uitvoer staat direct op je scherm.
- Last but not least: loggen naar een bestand. Dat bestand kun je (onder linux bijvoorbeeld) live volgen met "tail -f [bestandsnaam]".

[ Voor 43% gewijzigd door B-Man op 31-01-2008 13:11 ]

[Niemand_Anders]

Waarom gebruik je niet gewoon

SQL:

update artikel set artikelnummer = right(concat('000000000' + artikelnummer), 9) where length(artikelnummer) <= 9

Met deze update query worden alle nummers voorzien van voorloopnullen. Artikelnummers welke langer dan 9 tekens zijn, worden met rust gelaten.

Query gewoon rechtstreeks op database uitvoeren.

[MSalters]

In SQL, maar een tijdelijke tabel gebruiken? Daar gebruik je dan twee nummerkolommen in, 1 met de eerste 7 cijfers, en 1 met de laatste 2 cijfers.

[Swaptor]

Niemand_Anders schreef op donderdag 31 januari 2008 @ 13:17:
Met deze update query worden alle nummers voorzien van voorloopnullen.

Crazybyte schreef op donderdag 31 januari 2008 @ 13:01:
Echter nu komt er niet gewoon een '0' voor of achter maar ertussen.

4000 rijen in 10 minuten is overigens wel erg heftig, ik ben benieuwd waar het aan ligt. Ik vermoed overigens inefficient coderen in PHP.

[Cartman!]

even een veld erbij maken met 'updated' die standaard op '0' staat en als je een veld gehad hebt zet je hem op 1. Dan laat je je script alleen zoeken naar records die updated op 0 hebben staan.

[ikke007]

Zou het niet handiger zijn om de artikelnummers een integer getal te maken, en de presentatie aan te passen naar 0000000001?

[Crazybyte]

B-Man schreef op donderdag 31 januari 2008 @ 13:06:
4000 regels in 10 minuten? Dat is wel heel erg lang. Laat anders eens wat code zien, want ofwel je database is verschrikkelijk druk/traag, of je code is inefficient.

Ik gok op code die inefficiënt is, alhoewel de database zelf ook niet bepaald snel is. Drukte zou het niet aan mogen liggen, aangezien ik nu nog aan het testen ben in een aparte database waar verder niemand aan komt.

PHP:

<?php
// Alle errors tonen
error_reporting(6143);

// Connectie string voor Pervasive database
$dsn = "Driver={Pervasive ODBC Client Interface};ServerName=<servernaam>;dbq=<database>;"; 

// Verbinding maken met database
$dbh = odbc_connect($dsn,"","");

// We gaan het ophalen en update in 10 stappen doen, telkens een range van 100.000 artikelnummers
for($i = 0; $i<=9; $i++)
{
  // Artikelnummers ophalen
  $artikelnummers = array();
  if($i != 9)
  {
    $start = "0".$i."0000000";
    $end = "0".($i+1)."0000000";
  }
  else
  {
    $start = "0".$i."0000000";
    $end = "999999999";
  }
  
  // Artikelnummers ophalen en opslaan in array $artikelnummers  
  $sql = "SELECT artikelnummer FROM artikel WHERE artikelnummer > '$start' and artikelnummer <= '$end' and length(artikelnummer) = 9";
  $result = odbc_exec($dbh, $sql);
  while($row = odbc_fetch_object($result))
  {
    $artikelnummers[]= $row->ARTIKELNUMMER;
  }
  
  // Artikelnummers veranderen
  // Transactie starten
  $odbc_error = 0;
  odbc_autocommit($dbh, FALSE);
  
  // Query's uitvoeren
  foreach($artikelnummers as $artikelnummer)
  {
    $nieuwnummer = substr($artikelnummer, 0, 7) . "0" . substr($artikelnummer, 7);
    $sql = "UPDATE artikel SET artikelnummer = '$nieuwnummer' WHERE artikelnummer = '$artikelnummer';";
    odbc_exec($dbh, $sql) or $odbc_error++;
  }

  // Zijn er fouten? Dan terugdraaien anders vastleggen
  if($odbc_error == 0)
  {
    odbc_commit($dbh);
    echo("artikelnummers in range $start tot $end geupdate");
  }
  else
  {
    odbc_rollback($dbh);
    echo("artikelnummers in range $start tot $end niet geupdate");
  }
?>

Er zit wat overbodige code tussen, zoals het eerst uitlezen naar een array en het gebruik van transacties, maar het array zou niet zoveel mogen uitmaken en voor het gebruik van transacties heb ik gekozen omdat ik hem enkel wil vastleggen wanneer alles goed gegaan is. Op de test database is dat op zich niet nodig, maar ik wil op de live database niet straks ineens ergens tegen een fout aan lopen en dat ik dan ergens middenin opnieuw kan beginnen.

B-Man schreef op donderdag 31 januari 2008 @ 13:06:
Overigens is het juist handig om dit wel met een SQL query te doen, aangezien dat je 250.000 records naar PHP kopieren scheelt. De database kan dat alles zelf in-memory doen.
Plaats anders meteen ook de SQL query die je geprobeerd hebt, en geef aan wat niet werkt.

Het probleem is dat als het erg lang duurt, ik de live database pas na werktijd kan doen, omdat er niemand meer in het systeem mag zitten. Met de huidige tijdsduur zou ik dan het liefst een script schrijven dat gewoon 1 voor 1 alle tabellen langs gaat en daar alles update. Zo hoeft ik niet zelf de query's 1 voor 1 moet uitvoeren en dan moet zitten wachten tot het klaar is.

Wanneer het echter via SQL veel sneller is, dan zal ik daar natuurlijk gewoon voor gaan zitten, dan maar een uurtje of wat overwerken.

Niemand_Anders schreef op donderdag 31 januari 2008 @ 13:17:
Waarom gebruik je niet gewoon

* knip *

Met deze update query worden alle nummers voorzien van voorloopnullen. Artikelnummers welke langer dan 9 tekens zijn, worden met rust gelaten.

Waarom kun jij niet lezen?

Crazybyte schreef op donderdag 31 januari 2008 @ 13:01:
Echter nu komt er niet gewoon een '0' voor of achter maar ertussen.

Als het er simpelweg voor of achter had gemoeten, dan had ik de query's van mijn voorganger kunnen gebruiken.

MSalters schreef op donderdag 31 januari 2008 @ 13:33:
In SQL, maar een tijdelijke tabel gebruiken? Daar gebruik je dan twee nummerkolommen in, 1 met de eerste 7 cijfers, en 1 met de laatste 2 cijfers.

Zou een optie kunnen zijn.

Cartman! schreef op donderdag 31 januari 2008 @ 13:42:
even een veld erbij maken met 'updated' die standaard op '0' staat en als je een veld gehad hebt zet je hem op 1. Dan laat je je script alleen zoeken naar records die updated op 0 hebben staan.

Het kan maar het lijkt me niet echt handig, het gaat om zo'n 15 á 20 tabellen waar de nummers in voorkomen, die ik dan allemaal zou moeten aanpassen.

ikke007 schreef op donderdag 31 januari 2008 @ 13:59:
Zou het niet handiger zijn om de artikelnummers een integer getal te maken, en de presentatie aan te passen naar 0000000001?

Nee, want er zijn niet alleen maar numerieke artikelnummers, daarnaast moet er ergens een 0 tussen en niet voor of achter.

[ Voor 8% gewijzigd door Crazybyte op 31-01-2008 14:11 ]

[sam.vimes]

Niemand_Anders schreef op donderdag 31 januari 2008 @ 13:17:
[...]
Artikelnummers welke langer dan 9 tekens zijn, worden met rust gelaten.
[...]

Correctie: Artikelnummers welke langer dan 9 tekens zijn worden met de voorbeeldquery afgekapt op de 9 rechter tekens. Ik denk dat je nog even een

SQL:

where length(artikelnummer) < 9

moet toevoegen

edit:
Zie nu dat de nullen in het midden moeten komen, maar mijn opmerking blijft staan

[ Voor 10% gewijzigd door sam.vimes op 31-01-2008 14:54 ]

[Crazybyte]

B-Man schreef op donderdag 31 januari 2008 @ 13:06:
Plaats anders meteen ook de SQL query die je geprobeerd hebt, en geef aan wat niet werkt.

Overigens de query die ik al eens geprobeerd had, maar een foutmelding gaf was:

SQL:

UPDATE "ARTIKEL" 
 SET artikelnummer = CONCAT(CONCAT(left(artikelnummer,7), '0'), right(artikelnummer, 2))
 WHERE artikelnummer > '000000000' and artikelnummer <= '999999999' and length(artikelnummer) = '9'

Hij gaf dan een error "data truncated, effected 1 row(s)", het probleem blijkt overigens te zijn dat de software spaties toevoegt aan het veld. Dit kan maximaal 15 tekens bevatten en als het artikelnummer korter is, vult het ERP pakket dit aan met spaties.

Ik heb daarna geprobeerd om met rtrim() ervoor te zorgen dat hij de spaties eerst weghaalde, maar ook daar ging wat mis, mijn artikelnummer was ineens 2 plekken korter.
Betreffende query:

SQL:

UPDATE "ARTIKEL" 
 SET artikelnummer = CONCAT(CONCAT(left(artikelnummer,7), '0'), right(rtrim(artikelnummer), 2))
 WHERE artikelnummer > '000000000' and artikelnummer <= '999999999' and length(artikelnummer) = '9'

Toen was ik dus overgestapt naar PHP, echter heb net nog eens een tijdje in de help file gelezen en de volgende query blijkt wel het gewenste resultaat te geven.

SQL:

UPDATE "ARTIKEL" 
 SET artikelnummer = CONCAT(CONCAT(left(artikelnummer,7), '0'), substring(artikelnummer, 8, 2))
 WHERE artikelnummer > '000000000' and artikelnummer <= '999999999' and length(artikelnummer) = '9'

Ik ga nu nog maar eens proberen of dit goed aan te sturen is via PHP en sneller gaat, want dan kan ik via een PHP pagina alsnog mooi 1 voor 1 de tabellen laten aflopen. Als dat goed gaat kan ik hem namelijk ook automatisch over de live database laten lopen.

[ Voor 10% gewijzigd door Crazybyte op 31-01-2008 15:07 . Reden: Reactie op sam.vimes verwijderd, hij bleek al gezien te hebben dat die query sowieso fout was ;) ]