[PHP/MySQL] Snelle fetching van grote datasets

[Rekcor]

Ik heb een MySql-databasetabel met >10.000 records.

phpMyAdmin heeft welgeteld 0.0027 sec nodig om deze uit de tabel te trekken. Mijn superscript, te weten:

PHP:

  /**
  * Performs any query
  * @param [string] SQL query
  * @return [resource] Mysql result resource
  */
  function query($sql)
  {
    $result = mysql_query($sql, $this->getConnectionId());
    if ($result)
    {
      return $result;
    }
    else
    {
      $this->error('Error performing query: ' . mysql_error() . '<BR>SQL: '.$sql);
      return FALSE;
    }
  }

  /**
  * Find array of values from database
  * @param [string] sql query
  * @return [array]
  */
  function getArray($sql)
  {    
    $db_rs = $this->query($sql);
    $num_fields = mysql_num_fields($db_rs);
    while ($row = mysql_fetch_array($db_rs, MYSQL_ASSOC))
    {
      for ($i = 0; $i < $num_fields; $i++)
      {
        $result[mysql_field_name($db_rs, $i)][] = $row[mysql_field_name($db_rs, $i)];
      }
    }
    return $result;
  }

Doet er echter 0.980286121372 seconden over, een slordige factor 363 langer dus.

Mijn vraag: wat is er zo langzaam aan bovenstaande code?

[mithras]

Ik denk dat niet eens de query() langzaam is, maar dat het aan je doorlopen van je resultaten ligt. Benchmark eens met ook de tijden van de query() en die van getArray()

Ow, en wellicht heeft phpmyadmin een cache file waar jij die niet hebt?

[ Voor 17% gewijzigd door mithras op 12-03-2007 18:36 ]

[Gomez12]

Hebben we het wel over dezelfde query??? Want standaard gooit phpmyadmin er een limit overheen, wat het ietsje sneller maakt.

[Verwijderd]

Een gok: phpMyAdmin gebruikt paging, dus worden niet alle records daadwerkelijk opgehaald...

[Rekcor]

Mmm

Gomez12 schreef op maandag 12 maart 2007 @ 18:31:
Hebben we het wel over dezelfde query??? Want standaard gooit phpmyadmin er een limit overheen, wat het ietsje sneller maakt.

Mmm... die limit, die was ik idd vergeten. Zonder limit haalt phpMyAdmin het niet eens binnen de 30 sec execution time

Maar goed mensen, 10.000 rijen uit een database trekken is toch niet teveel gevraagd voor PHP? Dat moet toch in <1 sec gepiept zijn? Of overvraag ik PHP hiermee?

[Verwijderd]

Wat is dat nou voor vraag? Het gaat nou eenmaal niet sneller. 10.000 records opvragen is niet niks. Ik vraag me sterk af waar je dat dan voor nodig denkt te hebben.

[RobIII]

Rekcor schreef op maandag 12 maart 2007 @ 18:44:
Maar goed mensen, 10.000 rijen uit een database trekken is toch niet teveel gevraagd voor PHP? Dat moet toch in <1 sec gepiept zijn? Of overvraag ik PHP hiermee?

Mijn vraag is dan meteen: wat moet je in godesnaam met 10.000 records Je bent bekend met where-clauses? En als je er door heen gaat zitten itereren kan het inderdaad wel eens 'lang' gaan duren (wat is 'lang'?)

[ Voor 3% gewijzigd door RobIII op 12-03-2007 18:51 ]

[ACM]

PHP:

    while ($row = mysql_fetch_array($db_rs, MYSQL_ASSOC))
    {
      for ($i = 0; $i < $num_fields; $i++)
      {
        $result[mysql_field_name($db_rs, $i)][] = $row[mysql_field_name($db_rs, $i)];
      }
    }

Ik vind dat ook wel een vage constructie... Doe dan op zijn minst zoiets:

PHP:

    while ($row = mysql_fetch_array($db_rs, MYSQL_ASSOC))
    {
      foreach($row as $key => $value)
      {
        $result[$key][] = $value;
      }
    }

Scheelt je iig weer 2 x 10.000 x (het aantal kolommen) aan mysql_field_name-calls

Maar sowieso vind ik het maar een rare array-structuur om het standaard zo aan te leveren.

[Rekcor]

ACM schreef op maandag 12 maart 2007 @ 19:14:
Ik vind dat ook wel een vage constructie... Doe dan op zijn minst zoiets:

PHP:

    while ($row = mysql_fetch_array($db_rs, MYSQL_ASSOC))
    {
      foreach($row as $key => $value)
      {
        $result[$key][] = $value;
      }
    }

Scheelt je iig weer 2 x 10.000 x (het aantal kolommen) aan mysql_field_name-calls

Maar sowieso vind ik het maar een rare array-structuur om het standaard zo aan te leveren.

Thanks, scheelt een heleboel idd!

Ik heb een webapplicatie gemaakt, en die ben ik nu aan het optimizen op snelheid, vandaar de grote dataset... Echt leerzaam, want je ontdekt meteen de 'zwakke' plekken in je code.

[orf]

Vervang fetch_array gelijk even voor fetch_assoc.
Fetch_array returned 2 arrays, een nummerieke en een associative. Je gebruikt alleen de laatste.
(micro optimalisatie, want de hoeveelheid data die uit je database komt is je echte bottleneck).

Je hebt nog niet verteld waarom je zo'n enorme set ophaalt. Die wil je toch niet op het scherm printen hoop ik?

[ACM]

orf schreef op maandag 12 maart 2007 @ 22:18:
Fetch_array returned 2 arrays, een nummerieke en een associative. Je gebruikt alleen de laatste.

Euh, wat is het verschil tussen mysql_fetch_array($r, MYSQL_ASSOC) en mysql_fetch_assoc($r) dan? Vziw is dat intern dezelfde code. Met MYSQL_BOTH oid (de default iig) zou het wel uitmaken.

[MisterData]

Als het puur een database-operatie is waarbij je de gegevens niet hoeft op te halen (of niet allemaal op dat moment iig) zou je een Stored Procedure kunnen overwegen (vanaf MySQL 5 ofzo). Dat runt op de database-server zelf, en dan bespaar je je de overhead en latency van de communicatie tussen database-server en web-server. SP's schrijf je in SQL, voor de details kun je terecht op mysql.com

[orf]

ACM schreef op maandag 12 maart 2007 @ 22:48:
[...]

Euh, wat is het verschil tussen mysql_fetch_array($r, MYSQL_ASSOC) en mysql_fetch_assoc($r) dan? Vziw is dat intern dezelfde code. Met MYSQL_BOTH oid (de default iig) zou het wel uitmaken.

oeps, geen verschil, had het tweede argument niet zien staan (en zie altijd mensen zonder 2e argument gebruiken op veel fora)

[Rekcor]

orf schreef op maandag 12 maart 2007 @ 22:18:
Vervang fetch_array gelijk even voor fetch_assoc.
Fetch_array returned 2 arrays, een nummerieke en een associative. Je gebruikt alleen de laatste.
(micro optimalisatie, want de hoeveelheid data die uit je database komt is je echte bottleneck).

Je hebt nog niet verteld waarom je zo'n enorme set ophaalt. Die wil je toch niet op het scherm printen hoop ik?

Eh... ja?. Op zich heb ik een grid (www.activewidgets.com) die zulke grote datasets aankan, maar echt snel wordt het er niet van.

Momenteel ben ik mijn systeem wat naar zijn limieten aan het 'pushen', om de code te optimaliseren. Hierbij ga ik uit van de veronderstelling dat 1 gebruiker x 10.000 regels lijkt op 10 gebruikers x 1.000 regels of 100 gebruikers x 100 regels (of 10.000 gebruikers x 1 regel )...

[ACM]

Dat is niet het geval. Er kan dusdanige overhead in andere delen van de code zitten dat je 10000 regels uiteindelijk de helft van de totale tijd kost. Met als gevolg dat als deze code verder lineair schaalt je bij 1000 regels er maar 45% tijdswinst optreed ipv de 90% die jij blijkbaar verwacht.

Er zit ook tijd in het starten van apache processen, het inlezen en voorbereiden van de php code, het maken van een verbinding met je database, etc, etc. Je kan dus niet zeggen dat deze 10.000 records lijken op een situatie met 100x100 of zelfs 10x1000. Ik zou proberen juist de tests zo gelijk mogelijk aan de werkelijkheid te houden als je de applicatie als geheel wilt optimaliseren.

Als je 'hot spots' ontdekt kan je dan uiteraard wel de gebruikte aantallen wat verhogen om de resultaten van een specifieke optimalisatie te beoordelen.

[Gomez12]

Rekcor schreef op dinsdag 13 maart 2007 @ 08:44:
[...]


Eh... ja?. Op zich heb ik een grid (www.activewidgets.com) die zulke grote datasets aankan, maar echt snel wordt het er niet van.

Momenteel ben ik mijn systeem wat naar zijn limieten aan het 'pushen', om de code te optimaliseren. Hierbij ga ik uit van de veronderstelling dat 1 gebruiker x 10.000 regels lijkt op 10 gebruikers x 1.000 regels of 100 gebruikers x 100 regels (of 10.000 gebruikers x 1 regel )...

Als je zoiets wilt testen dan doe ik over het algemeen gewoon even _GET variabelen opengooien, een leuke url bedenken ( zodat je dus een pagina krijgt met 100 regels ) en dan gewoon via een shell scriptje 100x wget opstarten. Gaat wat mij betreft veel makkelijker. ( Of als je al ergens een openbare website hebt staan gewoon de laatste 100 / 1000 opgevraagde pagina's uit de apache log replayen met x keer wget ) Krijg je een veel betrouwbaarder beeld van. Het kost je eventjes een sloot geheugen voor het x keer opstarten van wget. Maar ja, het resultaat is bij mij meestal wel redelijk overeenkomstig realiteit.