mardi 8 mars 2016

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?

Aucun commentaire:

Enregistrer un commentaire