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.

possible duplicate of [Bulk load data into sqlite?](http://stackoverflow.com/questions/697004/bulk-load-data-into-sqlite) — KingCrunch, Jan 09 '13 at 14:24
@KingCrunch its not duplicate .. what if you need to filter invalid values ??? The link you gave can only work for trusted values — Baba, Jan 12 '13 at 20:59
@Baba That would be something different, but the question doesn't contain a "filter", it contains oly a "inserting a CSV into SQLite database". Also the OP mentioned mentioned `.import` himself. If you are right, the OP should clarify his question, else it's a duplicate :) — KingCrunch, Jan 12 '13 at 23:08

Al. · Answer 1 · 2013-04-19T10:07:03.250

2

$link->beginTransaction();

$query = $link->prepare("INSERT INTO user_info (a, b) VALUES (?, ?)");
for (/* each row, 100.000 rows */)
{
//variable stuff + execute query
}

$link->commit();

This holds the writes until after the loop - much faster. You'll need to create db object like this: $link = new PDO('sqlite:your.db');

edit: Typo/paste correction

edited Apr 19 '13 at 10:07

answered Apr 19 '13 at 07:13

Al.

330
1
13

Also, it's worth noting that this may not produce the same speed-up on other DB systems - in fact, quite the opposite, since the DBMS will have to maintain a higher isolation level, and either acquire a lock or maintain a temporary version of the data. – IMSoP Apr 19 '13 at 10:04

score 0 · Accepted Answer · answered Jan 11 '13 at 18:11

0

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

answered Jan 11 '13 at 18:11

IMSoP

89,526
13
117
169

Right. And what about arrays? I have array like this: $data[$row][$cell]. Can I bind array element somehow? I mean dynamic row index. I had problems with binding array elements in C# and MSSQL, and i didn't used array here. – Kamil Jan 11 '13 at 19:27
I'm not sure what you mean. – IMSoP Jan 11 '13 at 20:05

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

2 Answers2