Writing CSV to MySql Slow speed

Question

I changed my xls file to csv format for write to MySql. Because I get 60 second timeout error when i tried to save fields from xls file to MySQL. Now when i write echo code i am getting all my data in just 5 seconds. But when i try to save it to MySQL 60 second is not enough. Here is my codes:

$csvfile = fopen("data.csv","r");
    fgetcsv($csvfile);
    while (($data = fgetcsv($csvfile))!==FALSE){
        $ekle = $dbpdo->prepare("INSERT INTO isletmeler (sno) VALUES ('".$data[0]."')");
        $ekle->execute();   
    }

What can i do to speed up the speed of saving data to MySql? I have 29 column in csv but i tried to save just 1 column for see if it save my all data. I have about 2700 fields in csv but it just saving about 1000 fields in 60 second.

https://stackoverflow.com/questions/5526917/how-to-do-a-batch-insert-in-mysql — clinomaniac, Mar 05 '18 at 19:12
if you mean `$ekle = $dbpdo->prepare("INSERT INTO isletmeler (1,2,3,4) VALUES ('1,2,3,4)` i know this. — Hüseyin Çiftçi, Mar 05 '18 at 19:16
No. I mean to load data in larger chunks so you do not have to run `execute()` so many times. Either you can create a batch and add everything to the batch and run it once or you can change the insert statement to insert more data. I mean like `$ekle = $dbpdo->prepare("INSERT INTO isletmeler (a,b,c) VALUES (1,2,3),(4,5,6),(7,8,9).....` — clinomaniac, Mar 05 '18 at 19:16
So basically, you will not be calling the `execute()` inside the loop (unless you want to control the batch sizes). — clinomaniac, Mar 05 '18 at 19:18
you mean like this? `while (($data = fgetcsv($csvfile))!==FALSE){ $ekle = $dbpdo->prepare("INSERT INTO isletmeler (sno) VALUES ('".$data[0]."')"); } $ekle->execute();` — Hüseyin Çiftçi, Mar 05 '18 at 19:20
No. That's not what I had in mind. Look at the answer posted. That is using a different approach but should also help with performance significantly. — clinomaniac, Mar 05 '18 at 19:22

score 0 · Answer 1 · answered Mar 05 '18 at 19:19

0

You could restructure the code so that the prepare is only done once...

$csvfile = fopen("data.csv","r");

fgetcsv($csvfile);
$ekle = $dbpdo->prepare("INSERT INTO isletmeler (sno) VALUES (?)");
while (($data = fgetcsv($csvfile))!==FALSE){
    $ekle->execute([$data[0]]);   
}

This reduces the need to re-prepare the statement each time.

answered Mar 05 '18 at 19:19

Nigel Ren

56,122
11
43
55

i tried your codes. it saved 774 columns. – Hüseyin Çiftçi Mar 05 '18 at 19:24
avarage saving in per second is 20 columns. – Hüseyin Çiftçi Mar 05 '18 at 19:27
hello. I am very sorry but my hosting has a problem for several days. So I couldnt work on your last codes. I am trying to connect every day, every hour. I see that you deleted your last codes? Please write it again. – Hüseyin Çiftçi Mar 10 '18 at 06:26

Writing CSV to MySql Slow speed

1 Answers1