Hi, In order to improve the underlying SQLite performance, I've changed some low level settings with PRAGMA commands:
PRAGMA synchronous=NORMAL; /* instead of default FULL value, see: http://www.sqlite.org/pragma.html#pragma_synchronous */ PRAGMA journal_mode=WAL; /* http://www.sqlite.org/pragma.html#pragma_journal_mode and http://www.sqlite.org/wal.html */ >From an implementation point of view, I did as explained in this thread: https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/IY5PlUf4VwE. I've got an OrmManager class (which is a singleton) which is used to get new sessions. *The bold lines are the ones I added to improve performance*. class OrmManager: """ OrmManager class Handles the database and provides an abstraction layer for it """ def __init__(self, database, metadata, db_type, echo=False): self.database = database self.session_maker = sessionmaker() if db_type == 'file': engine = create_engine('sqlite:///' + database, echo=echo, connect_args={'detect_types': sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES}, native_datetime=True, poolclass=NullPool ) elif db_type == 'memory': engine = create_engine('sqlite:///' + database, echo=echo, connect_args={'detect_types': sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES}, native_datetime=True, poolclass=SingletonThreadPool, pool_size=5 ) else: raise Exception("Unknown db_type: %s" % str(db_type)) metadata.create_all(engine) self.session_maker.configure(bind=engine, expire_on_commit=False) * session = self.session_maker()* * session.connection().execute("PRAGMA journal_mode=WAL")* * session.commit()* * session.close()* def get_session(self): """Gets ORM session""" session = self.session_maker() * session.connection().execute("PRAGMA synchronous=NORMAL")* return session I have two questions: 1- journal_mode pragma is persistent (according to sqlite doc) and should be done once but is there a way to pass the synchronous configuration to the engine and make it global instead of setting it every time my application gets a new session ? 2- Are there any performance settings I can tune at sqlalchemy and/or sqlite level to improve my db access speed ? Thanks a lot for your feedback, Pierre -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
