mass insert - prepared vs batch vs single

[Indyk]

Hallo Forum,

ich würde gern eure Meinung zu diesem Thema hören, ich habe heute ein paar tests gemacht wie ich am schnellsten viele daten in die (MySQL ISAM) DB schreiben kann. Dazu habe ich mit dem PDO 3 varianten ausprobiert.

Prepared Statment:
Hier wird an Array an Daten übergeben, einmal prepared und dann alles in einer schleife gegen die datenbank gefeuert

PHP-Code:

public function insert_prepared($array)
{
        $sql = "
INSERT INTO amazon_bluebox_html
    (`id`, `text`, `date`)
VALUES
    (:id, :text, NOW())
";        
        $stmt = $this->pdo->prepare($sql);
        if (!$stmt)
            $this->debug($this->pdo->errorInfo());
            
        foreach ($array as $data) {
            
            $stmt->execute(array(
                ':asin' => $data['id'],
                ':html' => $data['text'],
            ));
            
            if ($stmt->errorCode() != '00000') {
                $this->debug($stmt->errorInfo());
            }
        }
} 


Batch Statement
Hier übergebe ich an Array aus datan baue ein großes statement zusammen und feuer einmal gegen die Datenbank

PHP-Code:

public function insert_batch($array)
{
        $sql = "
INSERT INTO amazon_bluebox_html
    (id, text, date)
VALUES
";        
        $format = "('%s', '%s', NOW())";
        $aSql    = array();
        foreach ($array as $data) {
            $aSql[] = sprintf($format, $data['id'], $data['text']);
        }
        
        $sql.= implode(",\n", $aSql);
        #echo '<pre>'.$sql.'</pre>';
        $stmt = $this->pdo->query($sql);    
} 


Single Statement
Hier werden die Daten direkt übergeben und gespeichert

PHP-Code:

public function insert_single($data)
{
        $sql = "
INSERT INTO amazon_bluebox_html
    (id, text, date)
VALUES
    (:id, :text, NOW())
";        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array(
            ':id'    => $data['id'],
            ':text'    => $data['text'],                    
        ));
} 


Dabei habe ich die tests einmal mit 1000, 50.000 und 100.000 Daten getestet, und das jeweils mit einem "cache" von 100 oder 1000 Daten für die zwei ersten Methoden.

Die Ergebnisse sehen wie folgt aus:

		prepared	batch		single
050.000/100	19s		8		35
050.000/1000	18s		7		35
100.000/100	37s		14		63
100.000/1000	37s		13		62

Was ist jetzt daraus zu schließen? das ein Großes Statement um einiges Schneller verarbeitet wird als viele kleine. Als PDO user hätte ich gedacht das ein prepared statement etwas mehr bringt aber es ist ja auch um einiges schneller als einzelne speichern von daten.

Leider bleibt beim Stapelinsert das lästige quoten von inhalten.

Am meisten verwundert hat mich der geringe unterschied zwischen 100 und 1000 blöcken.

Ein Vorteil des prepared Statements das man auf Informationen jedes einzelnen Statements zugreifen kann und nicht über eine große SQL debugen muss.

Wie handhabt ihr das? Habt ihr ähnliches erfahrungen und oder Tipps? Freu mich auf eure Antworten.

[cortex]

die ergebnisse sind nicht überraschend und entsprechen dem, was man als belesener entwickler erwarten würde. dennoch ist es schön, wieder und wieder gepredigte DOs + DONTs mit ein paar zahlen untermauert zu sehen .-)

dem entsprechend sollte man sql-statements zusammenklöppeln, bevor sie an die db geschickt werden:

PHP-Code:

$set = null;

foreach( $data as $col => $val )
{    
    $set .= ( $set != null ) ? ", " : null;

    $set .= $col . " = " . "'" . sprintf ( '%s' , mysql_real_escape_string( $val ) ) . "'" ;
}

$que =    

"    
    UPDATE table
    SET $set
    [ WHERE ... ]
"; 


prinzipiell meide ich sql-abfragen in schleifen wie der teufel das weihwasser. dennoch lassen sie sich nicht immer ohne weiteres umgehen bspw. bei einem UPDATE mehrerer zeilen mit unterschiedlichen werten. mit seitenblick auf den aufwand und die anzahl der betreffenden zeilen kann man natürlich tricksen:

a) mit temporären tabellen: artikel
b) mit case-anweisung: artikel

cx

[Indyk]

Hallo cortex,

danke für deine Antwort und das du als belesener Entwickler die Artikel mit mir (uns) geteilt hast. (- :

[cortex]

aber gern doch. weitere interessante artikel gibt's auch hier immer wieder mal: MySQL Performance Blog.

cx