Bei 20000 Einträgen dauert das Befüllen auf herkömmlichem Wege auf einem RPI3 ca. 140 Sekunden. Optimiert dauert es ca. 1.5 Sekunden!!!
Code, um Zeitaufwand für Inserts zu vergleichen.
function my_autoloader($class)DBCache Klasse, gefunden hier.
{
include 'include/' . $class . '.php';
}
$cache=5000;
$usecache = true;
$inserts=10000;
spl_autoload_register('my_autoloader');
$db = new \PDO('mysql:dbname=newdb;host=localhost', 'DB', 'PASSWORD'');
for ($i = 1; $i <= $inserts; $i++)
{
$data[] = ["id" => 1, "zip" => "30159", "name" => "Hannover", "citizens" => 50000];
}
$beginn = microtime(true);
$sql = 'INSERT IGNORE INTO testdaten (id, zip, name, citizens) VALUES ';
if ($usecache)
{
$dbCache = new dbcache($db, $sql, $cache, true);
foreach ($data as $entry)
{
$dbCache->add('(null,' . $db->quote($entry['zip']) . ',' . $db->quote($entry['name']) . ',' . ((int) $entry['citizens']) . ')');
}
$dbCache->flush();
}
else
{
$cache=0;
foreach ($data as $entry)
{
$sql2= $sql . " ('null','" . $entry['zip'] . "','". $entry['name'] . "','" . $entry['citizens'] . "')";
//echo "debug: $sql"; die;
$db->query($sql2);
}
}
$dauer = microtime(true) - $beginn;
echo "Verarbeitung des Skripts: $dauer Sek.<br>";
$sql = "SELECT count(*) from testdaten";
$result = $db->query($sql);
echo "Anzahl der Datensätze: " . $result->fetchColumn() . "<br>";
$db->query("TRUNCATE table testdaten");
$db->query("INSERT INTO performance (inserts, zeit, cache) VALUES"
. "('$inserts','$dauer','$cache')");
class dbcache
{
public $lastQuery;
private $db, $limit, $sql, $cache = [], $auto;
/**
* @param \PDO $db
* @param string $sqlPrefix
* @param int $limit
* @param bool $autoFlush
*/
public function __construct(\PDO $db, $sqlPrefix, $limit, $autoFlush = false)
{
$this->db = $db;
$this->limit = $limit;
$this->auto = $autoFlush;
$this->sql = $sqlPrefix;
}
/**
* @param string $query
*/
public function add($query)
{
$this->cache[] = $query;
if ($this->auto) {
$this->softFlush();
}
}
/**
* Flush cache and query database.
*/
public function flush()
{
if (empty($this->cache)) {
return;
}
// save query for logging, if something goes wrong
$this->lastQuery = $this->sql.implode(',', $this->cache);
$this->db->query($this->lastQuery);
$this->cache = [];
}
/**
* Flush cache if limit is reached.
*/
public function softFlush()
{
if (count($this->cache) >= $this->limit) {
$this->flush();
}
}
}
