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.

@KingCrunch its not duplicate .. what if you need to filter invalid values ??? The link you gave can only work for trusted values — Baba
– Baba, Commented Jan 12, 2013 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
– KingCrunch, Commented Jan 12, 2013 at 23:08

Al. · Accepted Answer · 2013-04-19 10:07:03Z

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, 2013 at 10:07

answered Apr 19, 2013 at 7:13

Al.

3301 silver badge13 bronze badges

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

1 Comment

IMSoP Over a year ago

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 · Accepted Answer · 2013-01-11 18:11:03Z

0

+50

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, 2013 at 18:11

IMSoP

99.7k18 gold badges135 silver badges183 bronze badges

2 Comments

Kamil Over a year ago

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.

IMSoP Over a year ago

I'm not sure what you mean.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related