Inserting CSV data to SQLite in PHP PDO - is there any faster method than insert row by row?

Question

Im looking for efficient method for inserting CSV data into SQLite database on remote server.

I have to use PHP. Im using SQLite3, so i have to use PDO (sqlite_query will not work).

CSV is on server side and it has 100000+ rows (50MB+ filesize).

Question: is there any method for PHP faster than this?

for (/* each row, 100.000 rows */)
{
    $a = 'something';
    $b = 'something';
    $query = $link->prepare("INSERT INTO user_info (a, b) VALUES (?, ?)");
    $query->bindParam(1, $a);
    $query->bindParam(2, $b);
    $query->execute();
}

My SQL file is in the same directory.

I've read about .import command, but i don't know how to use it with PDO (shall i use prepare? how file path should look like?).

Im new to PDO and SQLite.

Answer 1

Well, I don't know if there is a bulk approach with SQLite that can be used via a PDO driver, but you are re-preparing the same statement on every loop.

This would probably be a touch more efficient:

$query = $link->prepare("INSERT INTO user_info (a, b) VALUES (?, ?)");
for (/* each row, 100.000 rows */)
{
    $a = 'something';
    $b = 'something';

    $query->bindParam(1, $a);
    $query->bindParam(2, $b);
    $query->execute();
}