Issue: Inserting billions of rows
Author: stefdoerr
State: closed
Created: 2018-05-28 15:32:19

Hi, I wanted to try out peewee for the first time but I hit a snag at inserting ~20 billion rows. With the python sqlite3 library it works super fast (~1.5 minutes).

With peewee I don't see any progress. Neither does the file grow nor does it finish within a reasonable time-frame and there is no indication of progress.

I don't know if I'm doing something really bad. The only difference is that I could not create a double-column primary key in Data so I made a double-column index instead.

Any help would be appreciated :)

```python
import os
import numpy as np
import h5py
from tqdm import tqdm
import peewee as pw

db = pw.SqliteDatabase(dbpath)

class WorkUnit(pw.Model):
    name = pw.CharField()

    class Meta:
        database = db

class Data(pw.Model):
    x1 = pw.CharField()
    x2 = pw.CharField()
    x3 = pw.ForeignKeyField(WorkUnit, null=True)
    x4 = pw.FloatField(null=True)
    x5 = pw.FloatField(null=True)
    x6 = pw.BooleanField(null=True)
    x7 = pw.BooleanField(null=True)

    class Meta:
        indexes = (
            (('x1', 'x2'), True),  # True for unique
        )
        database = db

db.connect()

def createTables():
    db.create_tables([WorkUnit, Data])

def initializeDB(dataset):
    data = []
    fields = [Data.x1, Data.x2, Data.x5, Data.x6, Data.x7]
    with h5py.File(dataset, 'r') as infile:
        groups = list(infile)
        for group in tqdm(groups):
            for i, myval in enumerate(np.array(infile[group]['myval'])):
                data.append((group, 'c{0:06d}'.format(i), myval, False, False))
    with db.atomic():
        Data.insert_many(data, fields=fields).execute()
```

I tried moving the `with db.atomic():` inside the `for group in tqdm(groups):` loop which is how I had written it in `sqlite` and the progress bar said that it would take 6 hours to complete versus 2 minutes in `sqlite`

```python
    with h5py.File(dataset, 'r') as infile:
        groups = list(infile)
        for group in tqdm(groups):  # Around 50k iterations
            data = []
            for i, myval in enumerate(np.array(infile[group]['myval'])):
                data.append((group, 'c{0:06d}'.format(i), myval, False, False))
            with db.atomic():
                Data.insert_many(data, fields=fields).execute()
```

This is how the code looks like in `sqlite`

```python
c.execute('CREATE TABLE workunits (wuID INTEGER PRIMARY KEY, name TEXT DEFAULT NULL)')
conn.commit()
c.execute('''CREATE TABLE data (x1 TEXT NOT NULL, 
                                      x2 TEXT NOT NULL, 
                                      x3 INTEGER, 
                                      x4 REAL DEFAULT NULL, 
                                      x5 REAL DEFAULT NULL, 
                                      x6 INTEGER DEFAULT NULL, 
                                      x7 INTEGER DEFAULT NULL,
                                      FOREIGN KEY(x3) REFERENCES workunits(wuID),
                                      PRIMARY KEY(x1, x2)
                                      )''')
conn.commit()
conn.close()

with h5py.File(dataset, 'r') as infile:
    groups = list(infile)
    for group in tqdm(groups):
        rdata = [(group, 'c{0:06d}'.format(i), 0, val, 0, 0) for i, val in enumerate(np.array(infile[group]['val']))]
        c.executemany("INSERT INTO data VALUES (?, ?, NULL, ?, ?, ?, ?)", rdata)
```

I have a feeling that the answer is: "use NoSQL" :grin: 

My personal answer will never be to use NoSQL! Peewee can handle this, but it does impose some overhead. Here are the docs on ideas for speeding-up bulk inserts:

http://docs.peewee-orm.com/en/latest/peewee/querying.html#bulk-inserts

[Your comment](https://github.com/coleifer/peewee/issues/1615#issuecomment-392557450), in which you use a transaction and insert_many() looks good but you may be hitting practical limits with SQLite's ability to parameterize the data. Peewee also may be getting in the way by ensuring/validating data-types before building up the query. You can always profile the code to see what's going on.

Using `executemany()` with a sqlite3 cursor is just fine, too. Peewee database objects have a `cursor()` method with exposes a DB-API cursor. So this should work:

```python
db = pw.SqliteDatabase(...)
db.create_tables([WorkUnit, Data])
cursor = db.cursor()

with h5py.File(dataset, 'r') as infile:
    groups = list(infile)
    for group in tqdm(groups):
        rdata = [(group, 'c{0:06d}'.format(i), 0, val, 0, 0) for i, val in enumerate(np.array(infile[group]['val']))]
        cursor.executemany("INSERT INTO data VALUES (?, ?, NULL, ?, ?, ?, ?)", rdata)
```

Lastly, you can try batching the inserts:

```python
    with h5py.File(dataset, 'r') as infile:
        groups = list(infile)
        for group in tqdm(groups):  # Around 50k iterations
            data = []
            for i, myval in enumerate(np.array(infile[group]['myval'])):
                data.append((group, 'c{0:06d}'.format(i), myval, False, False))

            with db.atomic():
                for i in range(0, len(data), 100):  # INSERT 100 rows at-a-time
                    Data.insert_many(data[i:i+100], fields=fields).execute()
```

Great! Thanks for the input :) I will give it a try tomorrow.

So I tried the peewee cursor
```python
cursor = db.cursor()
cursor.executemany("INSERT INTO data VALUES (?, ?, NULL, ?, ?, ?, ?)", rdata)
```
versus the sqlite cursor
```python
conn = sqlite3.connect(dbpath)
c = conn.cursor()
c.executemany("INSERT INTO data VALUES (?, ?, NULL, ?, ?, ?, ?)", rdata)
```
and the first one is still orders of magnitude slower. I don't know what the cause is since I can't profile very easily on the machine I'm currently on but in any case it seems I'll just mix'n'match between peewee and sqlite3 for the moment.


That's very strange, since the first one is literally the same as the second. It *may* have something to do with the way Peewee sets up the connection autocommit... you can try wrapping it in a transaction and seeing if that has any effect:

```python
with db.atomic():
    cursor.executemany(...)
```