Issue: Low performance compared to plain sqlite3
Author: piacias
State: closed
Created: 2014-04-15 08:19:39

Hi, I'm writing simple logparser. I have 3 models defined as below. I set database as shown: 

```
db = peewee.SqliteDatabase(DATABASE, autocommit=False)

class Entry(BaseModel):
    timestamp = peewee.DateTimeField()
    level = peewee.IntegerField()
    module = peewee.CharField()
    line = peewee.IntegerField()
    exception = peewee.CharField()
    description = peewee.CharField()
    orig_exception = peewee.CharField(null=True)
    orig_description = peewee.CharField(null=True)


class TracebackEntry(BaseModel):
    path = peewee.CharField()
    line = peewee.IntegerField()

class Traceback(BaseModel):
    entry = peewee.ForeignKeyField(Entry)
    traceback_entry = peewee.ForeignKeyField(TracebackEntry)
```

Input data are stored in logfile (tsv), each log entry contains some basic data and optional traceback - I'm using following method to process entry:
param entry is list which contains:
- some basic data - this line is required
- If entry has traceback - last line contains original exception (orig_exception, orig_description)
- Other lines - contain traceback entries (path + line)

```
def process_entry(entry):
    e1, e2, e3, e4, e5, e6= entry[0].split('\t')
    e = Entry(
        timestamp=e1,
        level=e2,
        module=e3,
        line=e4,
        exception=e5,
        description=e6
    )
    tb_entries = [] # this contains traceback entries objects to set m2m

    if len(entry) > 1:
        exc, exc_desc = entry[-1].split('\t')
        e.orig_exception = exc
        e.orig_description = exc_desc

        # collecting already defined traceback entries (it doesn't contain lots of data)
        defined_tb_entries = TracebackEntry.select()

        # gather traceback entries
        for l in entry[1:-1]:
            path, line = l.split('\t')
            found = False
            for tb in defined_tb_entries:
                if tb.path == path and tb.line == int(line):
                    found = True
                    tb_entries.append(tb)
                    break
                if not found:
                    # create missing 
                    tb_entries.append(TracebackEntry.create(path=path, line=line))
        e.save()

        # Set m2m Entry,TracebackEntry
        Traceback.insert_many(
                {
                    'entry': e,
                    'traceback_entry': tb_entry
                } for tb_entry in tb_entries
        ).execute()
```

I've got problem with performance while parsing big log file (800MB).
After parsing there are:
- 1102086 - Entry objects 
- 177 - TracebackEntry objects
- 3750785 - Traceback objects (m2m)
  stored in database.

When I'm using peewee, I get results in:
1:23:22.891102 - peewee - sqlite (file - not :memory:)
0:42:46.240624 - peewee - mysql

but when I change method 'process_entry' to use native sqlite3 module, I get results in:
0:08:22.793224 - sqlite3

What am I doing wrong, or how can I  speedup processing of entries.

Notice: I've simplified process_entry method to increase  readability (may not run without errors)


Have you tried running the import in a transaction? Peewee calls `commit()` after issuing `INSERT` by default, which may not be happening with sqlite by itself.

You would basically:

``` python
with db.transaction():
    process_entries()
```

Please give that a shot and let me know.


Ok, I've changed code to be executed as one transaction and result is very good:

0:11:30.584987 - peewee - sqlite

Please notice that I've had already autocommit set to False (on db initialization), so I'm not sure is 'commit' is it only thing.

Anyway, thanks for reply.