PonyORM, SQLite performance

I'm using PonyORM as ORM for my SQLite database with Python 3.5 on Raspberry PI 3 (not the fastest device you can imagine, but it shouldn't be that bad).

Some operations, for instance insertions, seem to be terribly slow. One insertion of entity containing 3 short strings can take 4 - 10 seconds.

Here is my datamodel.py file:

from pony.orm import *
from datetime import datetime

db = Database('sqlite', 'website.db', create_db = True)

class User(db.Entity):
    login = Required(str, unique=True)
    password = Required(str)
    actions = Set("Event")

class Event(db.Entity):
    description = Optional(str)
    date = Required(datetime)
    ip = Required(str)
    user = Optional(User)

db.generate_mapping(create_tables = True)

I've also created a really simple performance test:

from datamodel import *
from datetime import *

sql_debug(True)
totalTime = datetime.now()
with db_session:
    constructTime = datetime.now()
    Event(date = datetime.now(),
          ip = '0.0.0.0',
          description = 'Sample event!')
    constructTime = datetime.now() - constructTime
totalTime = datetime.now() - totalTime
print(constructTime)
print(totalTime)

The sample result of it:

GET NEW CONNECTION
BEGIN IMMEDIATE TRANSACTION
INSERT INTO "Event" ("description", "date", "ip", "classtype") VALUES (?, ?, ?, ?)
['Sample event!', '2016-03-08 23:05:15.066742', '0.0.0.0', 'Event']

COMMIT
RELEASE CONNECTION
0:00:00.000479
0:00:04.808138

The SQL query string is printed pretty quickly, so I guess translation isn't the problem here, but as you can see, the whole operation takes several seconds.

What can be the reason of it? Is there any way to improve this ridiculously long time?

Solution

I think that the problem is caused by slow SD card. During COMMIT execution SQLite flushes data to the card, and this operation may be slow with SD cards. It is a known problem that SQLite can work slow on Raspberry PI, especially if the card class is lower than Class 10: https://spin.atomicobject.com/2013/11/14/sqlite-raspberry-pi/

You can perform the following tests in order to check if PonyORM is responsible for slow speed in your case:

1) Try to use in-memory database. In order to do this, replace the line with the database object definition to the following line:

db = Database('sqlite', ':memory:', create_db=True)

2) Perform the same operations without using of PonyORM. I keep prints in order to check that they are not the reason of the slowness:

import sqlite3
from datetime import *

totalTime = datetime.now()
connection = sqlite3.connect('website.db', isolation_level=None)

sql = 'BEGIN IMMEDIATE TRANSACTION'
print(sql)
connection.execute(sql)

sql = 'INSERT INTO "Event" ("description", "date", "ip", "classtype") VALUES (?, ?, ?, ?)'
args = ('Sample event!', '2016-03-08 23:05:15.066742', '0.0.0.0', 'Event')
print(sql)
print(args)
connection.execute(sql, args)

sql = 'COMMIT'
print(sql)
connection.execute(sql)

totalTime = datetime.now() - totalTime
print(totalTime)

If performance problems are caused by SD card, the first test will executed instantly, and the second test will be as slow as with the ORM.

Maybe it is possible to achieve better performance by using SQLite pragmas PRAGMA synchronous = OFF and PRAGMA journal_mode = MEMORY. Currently PonyORM does not provide a way to set these options automatically, because with these options database file can be corrupted by sudden power failure.