Large number of SQLite inserts using PHP

Question

I have about 14000 rows of comma separated values that I am trying to insert into a sqlite table using PHP PDO, like so:

<?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
}

?>

This query takes a long time, and to run it without interuption, I would have to use PHP-CLI. Is there a better (faster) way to do this?

meouw · Accepted Answer · 2011-05-09 14:20:53Z

25

You will see a good performance gain by wrapping your inserts in a single transaction. If you don't do this SQLite treats each insert as its own transaction.

<?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();

?>

answered May 9, 2011 at 14:20

meouw

42.1k11 gold badges55 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kuhboeejoe Over a year ago

This worked beautifully for me. I was amazed at how fast it imported everything. Thanks.

F-3000 Over a year ago

To gain even more speed, use statement preparing in combination with transaction. Also makes things safer in case of values that might affect the actual query, like having unexpected quotes.

Saic Siquot · Accepted Answer · 2011-05-09 14:21:00Z

2

Start a transaction before the loop and commit it after the loop
the way your code is working now, it starts a transaction on every insert

answered May 9, 2011 at 14:21

Saic Siquot

6,5135 gold badges37 silver badges56 bronze badges

Comments

Tac Tacelosky · Accepted Answer · 2012-10-04 11:02:59Z

2

If you're looking for a bit more speed, use prepare/fetch, so the SQL engine doesn't have to parse out the text string each time.

$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();
}

It's counter-intuitive that you do the binding outside the loop, but this is one reason why this method is so fast, you're basically saying "Execute this pre-compiled query using data from these variables". So it doesn't even need to move the data around internally. And you want to avoid re-parsing the query, which is the problem if you use something like "insert into table (name) values ('$name')", every query sends the entire text string to the database to be re-parsed.

One more thing to speed it up -- wrap the whole loop in a transaction, then commit the transaction when the loop is finished.

answered Oct 4, 2012 at 11:02

Tac Tacelosky

3,4823 gold badges34 silver badges33 bronze badges

2 Comments

Thilo Over a year ago

Are there some conditions to get this to work? It does not seem to over at stackoverflow.com/questions/34878681/…

Eborbob Over a year ago

Well yes, it's fast, but it won't do what you want. You need to do the binding in the loop, your code will just insert the same value over and over.

Raveline · Accepted Answer · 2011-05-09 14:23:10Z

From SQLlite FAQ :

Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite.. [...]

Another option is to run PRAGMA synchronous=OFF. This command will cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. But if you lose power in the middle of a transaction, your database file might go corrupt.

I'd say this last paragraph is what you need.

EDIT: No sure about this, but I believe using sqlite_unbuffered_query() should do the trick.

Collectives™ on Stack Overflow

Large number of SQLite inserts using PHP

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related