У меня около 14000 строк разделенных запятыми значений, которые я пытаюсь вставить в таблицу sqlite с использованием PHP PDO, например:
<?php // create a PDO object $dbh = new PDO('sqlite:mydb.sdb'); $lines = file('/csv/file.txt'); // import lines as array foreach ($lines as $line) { $line_array = (','$line); // create an array of comma-separated values in each line $values = ''; foreach ($line_array as $l) { $values .= "'$l', "; } substr($values,-2,0); // get rid of the last comma and whitespace $query = "insert into sqlite_table values ($values)"; // plug the value into a query statement $dbh->query($query); // run the query } ?> Этот запрос занимает много времени, и для его запуска без interuption мне придется использовать PHP-CLI. Есть ли лучший (более быстрый) способ сделать это?
Вы увидите хороший прирост производительности путем упаковки ваших вставок в одну транзакцию. Если вы этого не делаете, SQLite рассматривает каждую вставку как свою собственную транзакцию.
<?php // create a PDO object $dbh = new PDO('sqlite:mydb.sdb'); // Start transaction $dbh->beginTransaction(); $lines = file('/csv/file.txt'); // import lines as array foreach ($lines as $line) { $line_array = (','$line); // create an array of comma-separated values in each line $values = ''; foreach ($line_array as $l) { $values .= "'$l', "; } substr($values,-2,0); // get rid of the last comma and whitespace $query = "insert into sqlite_table values ($values)"; // plug the value into a query statement $dbh->query($query); // run the query } // commit transaction $dbh->commit(); ?>
Запустите транзакцию перед циклом и завершите его после цикла
как ваш код работает сейчас, он начинает транзакцию на каждой вставке
Из SQLlite FAQ :
Скорость транзакций ограничена скоростью диска, потому что (по умолчанию) SQLite фактически ожидает, пока данные действительно будут сохранены на поверхности диска до завершения транзакции. Таким образом, если вы внезапно потеряете власть или если ваша ОС выйдет из строя, ваши данные по-прежнему будут в безопасности. Подробнее читайте об атомарной фиксации в SQLite .. […]
Другой вариант – запустить PRAGMA synchronous = OFF. Эта команда заставит SQLite не ждать, пока данные достигнут поверхности диска, что сделает операции записи намного быстрее. Но если вы потеряете власть в середине транзакции, ваш файл базы данных может испортиться.
Я бы сказал, что последний абзац – это то, что вам нужно.
EDIT : Не уверен в этом, но я считаю, что использование sqlite_unbuffered_query () должно сделать трюк.
Если вы ищете немного большую скорость, используйте prepare / fetch, поэтому SQL-движок не должен каждый раз анализировать текстовую строку.
$name = $age = ''; $insert_stmt = $db->prepare("insert into table (name, age) values (:name, :age)"); $insert_stmt->bindValue(':name', $name); $insert_stmt->bindValue(':age', $age); // do your loop here, like fgetcsv while (get the data) { list($name, $age) = split(',', $string); $insert_stmt->execute(); }
Контр-интуитивно понятно, что вы выполняете привязку вне цикла, но это одна из причин, почему этот метод работает так быстро, вы в основном говорите «Выполняйте этот предварительно скомпилированный запрос, используя данные из этих переменных». Поэтому даже перемещать данные не нужно. И вы хотите избежать повторного анализа запроса, что является проблемой, если вы используете что-то вроде «insert into table (name) values (« $ name »)», каждый запрос отправляет всю текстовую строку в базу данных, разобран.
Еще одна вещь, чтобы ускорить ее – обернуть весь цикл в транзакции, а затем зафиксировать транзакцию, когда цикл закончен.