# Save 3 million rows best practice

**URL:** <https://discourse.cakephp.org/t/save-3-million-rows-best-practice/4846>\
**Category:** Performance\
**Created:** [August 31, 2018, 8:34pm UTC](https://discourse.cakephp.org/t/save-3-million-rows-best-practice/4846 "2018-08-31T20:34:25Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![mesiarm](https://avatars.discourse-cdn.com/v4/letter/m/4af34b/32.png) [@mesiarm](https://discourse.cakephp.org/u/mesiarm)\
**Post date:** [August 31, 2018, 8:34pm UTC](https://discourse.cakephp.org/t/save-3-million-rows-best-practice/4846/1 "2018-08-31T20:34:25Z")

</div>

I want to save 3 million rows with saveMany into mysql database. This function is rare used to import convert and save data from csv. I want to ask about tips to do it efficiently. Should I call saveMany for bulks of (for example) 10 000 rows or is better to save it once? Any other tips?

---

<div class="post-metadata">

**Author:** ![rrd](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/rrd/32/113_2.png) [@rrd](https://discourse.cakephp.org/u/rrd)\
**Post date:** [September 1, 2018, 10:26am UTC](https://discourse.cakephp.org/t/save-3-million-rows-best-practice/4846/2 "2018-09-01T10:26:20Z")

</div>

Definitely you should use smaller chunks. Depending on the size of the data it may be 100 or 1.000 rows.

---

<div class="post-metadata">

**Author:** ![Graziel](https://avatars.discourse-cdn.com/v4/letter/g/71c47a/32.png) [@Graziel](https://discourse.cakephp.org/u/Graziel)\
**Post date:** [September 1, 2018, 11:57am UTC](https://discourse.cakephp.org/t/save-3-million-rows-best-practice/4846/3 "2018-09-01T11:57:57Z")

</div>

there was workshop about it quite a time ago but it should still be viable, if you want to know how it works you would need to watch it from begining

---

<div class="post-metadata">

**Author:** ![JoeBot](https://avatars.discourse-cdn.com/v4/letter/j/e9bcb4/32.png) [@JoeBot](https://discourse.cakephp.org/u/JoeBot)\
**Post date:** [September 6, 2018, 6:44am UTC](https://discourse.cakephp.org/t/save-3-million-rows-best-practice/4846/4 "2018-09-06T06:44:24Z")

</div>

Hello, you could also do bulk insert with load file in mysql.

You generate a csv file in weebroot directory.

And you can launch a function like that

```
    private function ImportAndResetFile(){
            TableRegistry::clear();
            ConnectionManager::alias('default', 'default');
            $new = ConnectionManager::get('default');
            $new->execute(
                "LOAD DATA INFILE 'c:/wamp64/www/yourproject/webroot/messages.csv' IGNORE 
                        INTO TABLE messages
                        FIELDS TERMINATED BY ',' 
                        ENCLOSED BY '\"'
                        ESCAPED BY '\"'
                        LINES TERMINATED BY '\r\n'");
}
```

---

<div class="post-metadata">

**Author:** ![mesiarm](https://avatars.discourse-cdn.com/v4/letter/m/4af34b/32.png) [@mesiarm](https://discourse.cakephp.org/u/mesiarm)\
**Post date:** [September 6, 2018, 6:57am UTC](https://discourse.cakephp.org/t/save-3-million-rows-best-practice/4846/5 "2018-09-06T06:57:44Z")

</div>

Good tip if I wouldn’t need to convert data before save

---

<div class="post-metadata">

**Author:** ![deanoj](https://avatars.discourse-cdn.com/v4/letter/d/bcef8e/32.png) [@deanoj](https://discourse.cakephp.org/u/deanoj)\
**Post date:** [September 6, 2018, 1:19pm UTC](https://discourse.cakephp.org/t/save-3-million-rows-best-practice/4846/6 "2018-09-06T13:19:40Z")

</div>

My CSV might be half a million rows, but here’s how I tackle it.

1. create a shell that will read the file X number of lines at a time. I usually just end up with an array of strings (each line in the CSV)

2. Create a model method to accept the array and persist the data using the ORM.

3. Take a sample of the CSV to build a test (both for the shell and a hard coded array for the model test)

4. Get my test passing, then I can swap out the ORM for a prepared statement if needed, as well as other optimisations - having confidence my logic is correct.