Whats the fastest between adding row by row and multiple row in MySQL

Question

I'm parsing a large text file to add it into mySQL database, the file is more than 10K lines, each lines with more than 20 columns (line seperated by ",").

Before I start coding, I wanted to know which is the best way (in term of memory and execution time) between those two solutions :

The easy way : Line by line

Adding row by row (more than 10K insert into) : The easiest way

Complicated way

Parsing the whole file, creating ArrayList for each column, then inserting all the data in only one statement.

Does the "complicated way" save me a lot in the execution time ?

Thanks

[How to import CSV file to MySQL table](https://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table) — Lukasz Szozda, Jul 06 '17 at 10:37
No. The correct way is [JDBC batching](https://stackoverflow.com/questions/2993251/jdbc-batch-insert-performance). When you process your `ArrayList`, you would then be inserting all the data one row at a time. — Elliott Frisch, Jul 06 '17 at 10:37
you might want to insert your data in batches, say 1K at a time for your example. — axnet, Jul 06 '17 at 10:39
`load data infile`: https://dev.mysql.com/doc/refman/5.7/en/load-data.html — Gordon Linoff, Jul 06 '17 at 10:41

score 0 · Answer 1 · answered Jul 06 '17 at 13:01

0

Thanks to the solutions of @Gordon Linoff I was able to do it :

The request to execute in Java was :

LOAD DATA LOCAL INFILE 'file.txt' INTO TABLE TableName FIELDS TERMINATED BY ';';

answered Jul 06 '17 at 13:01

Hamza Amami

94
4
21

Whats the fastest between adding row by row and multiple row in MySQL

1 Answers1