PonyORM, SQLite performance

Question

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?

Self-implemented timing algorithms are not reliable data-points. — OneCricketeer, Mar 08 '16 at 22:23
@cricket_007 Do you suggest that my method of measuring time is bad? Well maybe you are right, maybe it isn't accurate, but anyway the problem occurs in real aplication when it freezes for a few seconds to insert data into database. — Rames, Mar 08 '16 at 22:31
I'm just saying a number of limited runs on a single computer isn't very reliable for claiming there is a large speed difference. ORM in general, is much slower than raw queries, though. — OneCricketeer, Mar 08 '16 at 22:36
@cricket_007 I'm not claming that there is or there isn't difference between ORM and raw queries, I haven't tested that. The SQL query in question is output generated by ORM and it appears on the screen probably less than one second after starting the program, but that's just my observation, I don't know how to measure that in Python. — Rames, Mar 08 '16 at 22:42
Well, your `totalTime` includes the time to open and close the `db_session` resource and do a few date-math operations (not that those are slow). I imagine it is the open, insert, and close operations summed together that you are seeing being high — OneCricketeer, Mar 08 '16 at 22:58

score 4 · Accepted Answer · edited May 23 '17 at 12:31

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.

I have performed similar tests on my own, even before your answer. Insertions are fast in in-memory mode and still very slow even with raw SQL queries while using them with database in file. I tried to move the database file to pendrive and it improved insertion time dramatically, now it takes 100-500 ms. So you were right about SD card, however it's a bit surprising to me, because the database file is small and writing a file even 10 times larger than that takes only miliseconds. Anyway that's not PonyORM fault for sure - thank you for developing such good and user friendly software! — Rames, Mar 09 '16 at 14:07

PonyORM, SQLite performance

1 Answers1