[PHP/SQL] Performance van tabel backup tov PhpMyAdmin

[JayVee]

Hoi!

Ik probeer een hele tabel te backupen door eerst een

SQL:

SELECT * FROM tabelletje

te doen (de query duurt maar 0.052seconden) en dan de resultset in een string te zetten (extended INSERT). Dit is echter veel trager dan de PHPMyAdmin export (18s vs. 6s).

Ik heb uiteraard even naar pma de code gekeken en de volgende optimalisaties aangebracht
• Unbuffered queries gebruiken
• mysql_fetch_row ipv mysql_fetch_assoc
• over een result row heenlopen met for ipv foreach _{(vraag me af of dit wel sneller is, aangezien je de counter nu in PHP bijhoudt - deze bench toont echter dat for 10% sneller is dan foreach)}

Toch duurt mijn code nog steeds drie keer zo lang als die van pma. Helaas kan ik de sql export library van PMA niet even in mijn code zetten, aangezien die erg veel PMA functies en constantes gebruikt.

Voor de volledigheid hier de PHP code (al is die erg triviaal):

PHP:

$resultsQuery = "INSERT INTO new_table VALUES ";

$sql_results = mysql_unbuffered_query( "SELECT * FROM table" );
$numFields = mysql_num_fields( $sql_results );
while ( $row_results = mysql_fetch_row( $sql_results ) ) {
    $resultsQuery .= "(";
    for ( $i = 0; $i < $numFields; $i++ ) {
        $resultsQuery .= "'" . $row_results[$i] . "', ";
    }
    $resultsQuery = substr( $resultsQuery, 0, -2 ) . "),\n";
}
$resultsQuery = substr( $resultsQuery, 0, -2 ); // trim last ', '

Iemand een idee hoe je het snelste een tabel omzet naar een INSERT INTO query string? Aangezien PHPMyAdmin op dezelfde server (en instellingen) draait moet het aan de code zelf liggen. (toch? )

[SchizoDuckie]

al eens gehoord van MysqlDump

[JayVee]

Goed punt, was ik vergeten in de startpost te zetten. We hebben niet altijd shell access, dus mysqldump kunnen we niet gebruiken.

[Cavorka]

SchizoDuckie schreef op maandag 31 juli 2006 @ 17:53:
al eens gehoord van MysqlDump

En als je dat niet mag gebruiken? Zeg... bijvoorbeeld onder IIS, waar je rechten moeten hebben om cmd.exe uit te voeren om mysqldump uit te voeren.

phpMyAdmin gebruikt niet voor niets de SELECT * FROM `tabel` methode. Op die manier werkt het op elke machine.

[ Voor 11% gewijzigd door Cavorka op 31-07-2006 18:07 . Reden: Grrr, JayVee, beetje replyen! Tsk. ;) ]

[maurad3r]

Is de functie mysql_fetch_row niet slomer dan bv myql_fetch_assoc?

[JayVee]

Ah HA!
Ik heb de nodige optimalisatie gevonden! Scheelt een factor 10!

PHP:

$sql_results = mysql_unbuffered_query( "SELECT * FROM table" );
$numFields = mysql_num_fields( $sql_results ); 

// -- resultaten cachen --
$rows = array();
while ( $ar_results = mysql_fetch_row($sql_results) ) {
    $value = '';
    for ( $i = 0; $i < $numFields; $i++ ) {
        $value .= "'" . $ar_results[$i] . "', ";
    }
    $rows[] = substr( $value, 0, -2 ); 
}

// -- resultaten in quey string zetten --
$resultsQuery = "INSERT INTO " . $this->resultTableName . " VALUES ";
for ( $i = 0; $i < count($values); $i++ ) {
    $resultsQuery .= '(' . $values[$i] . '), ' . "\n";
}
$resultsQuery = substr( $resultsQuery, 0, -2 ); // trim last ', '

De truc is om niet de hele tijd strings te appenden (regel 8 in codevoorbeeld van startpost)! Ik zag dat dit steeds langer duurde.

Ik vermoed dat PHP de lengte van strings niet bijhoudt en elke keer bij het appenden de hele string langs loopt tot hij het eind (\0) tegen komt. Voor arrays houdt PHP wel een interne pointer bij, en dus is het appenden aan een array (zie regel 7 van de code hierboven) veel sneller dan het appenden aan een string! (Grappig genoeg append ik nog steeds strings in regel 17 van het voorbeeld hierboven, maar natuurlijk minder vaak (factor 10? ))

Dit is trouwens ook wat PHPMyAdmin doet, alleen heb ik nu pas door waarom!

[ Voor 6% gewijzigd door JayVee op 31-07-2006 22:22 ]

[chem]

ik zou zowel $value op regel 9 als $resultsQuery op regel 17 vervangen door arrays die je implode. Dat gaat nog heel veel sneller dan zelf te concatten.

[JayVee]

chem schreef op maandag 31 juli 2006 @ 21:43:
ik zou zowel $value op regel 9 als $resultsQuery op regel 17 vervangen door arrays die je implode. Dat gaat nog heel veel sneller dan zelf te concatten.

Je hebt gelijk. Regel 9 ga ik niet vervangen omdat die gesimplificeerde code is. Afhankelijk van o.a. het data type quote ik $value namelijk wel of niet (en doe ik misschien nog andere bewerkingen). Regel 17 ga ik echter optimaliseren.
_{Regel 9 wordt ook niet zo een lange string, dus daar is niet meer zo waanzinnig veel winst te halen, toch?}

// edit:
Hm, uiteraard kan je regel 9 ook nog met een array aanpakken en dan in regel 11 imploden. Zal eens kijken wat sneller is!

// edit 2:
Imploden is ook op regel 9 sneller, maar het verschil is echt heel erg klein. Ik zal zo de uiteindelijke code posten, voor de mensen die een MySQL table backup functie willen maken!

[ Voor 20% gewijzigd door JayVee op 31-07-2006 22:23 ]

[JayVee]

De uiteindelijke code (die bij lange na niet perfect is, maar aan mijn eisen voldoet):

PHP:

$sql_results = mysql_query( "SELECT * FROM tabelletje" );
while ( $ar_results = mysql_fetch_assoc( $sql_results ) ) {
    $ar = array();
    foreach ( $ar_results as $fieldName => $value ) {
        if ( $value == NULL ) {
            $ar[] = 'NULL';
        } else if ( wantToQuote($value) == true ) {   // deze functie graag zelf schrijven
            $ar[] = '\'' . addslashes( $value ) . '\'';
        } else {
            $ar[] = $value;
        }
    }
    $ar_values[] = implode( ', ', $ar );
}
$backupQuery = "INSERT INTO tabelletje " .
    " VALUES (" . implode( "),\n(", $ar_values ) . ");\n\n";

[ Voor 19% gewijzigd door JayVee op 31-07-2006 22:35 ]

[Erkens]

en nu ook nog hetzelfde doen voor $backupQuery

[JayVee]

Hoe bedoel je? $backupQuery is in een keer goed nu.

[Erkens]

ik zie

.=

[JayVee]

Ah, dat klopt. In de originele code maak ik nog een backup van de structuur van de tabel dmv

SQL:

SHOW CREATE TABLE tabelletje

[Observer]

Al aan "INSERT INTO `backup` SELECT * FROM `tabelletje`;" gedacht? Of moeten er specifieke handelingen worden verricht op de resultset?

[Erkens]

NCIS schreef op vrijdag 04 augustus 2006 @ 14:49:
Al aan "INSERT INTO `backup` SELECT * FROM `tabelletje`;" gedacht? Of moeten er specifieke handelingen worden verricht op de resultset?

ehm, en waar is die backup dan? je maakt hier alleen een copy van je tabel