On Jun 19, 2013, at 10:14 AM, pr64 <pierrerot...@gmail.com> wrote:
> 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.
> 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 ?
you want to use a connect event for that:
from sqlalchemy import event
@event.listens_for(my_engine, "connect")
def on_connect(dbapi_conn, conn_rec, conn_proxy):
cursor = dbapi_conn.cursor()
cursor.execute("your pragma here")
cursor.close()
> 2- Are there any performance settings I can tune at sqlalchemy and/or sqlite
> level to improve my db access speed ?
I'm not familiar with the nature of these performance settings, but the sqlite3
DBAPI and SQLite itself is extremely fast, way way faster by itself than if you
have any kind of Python code wrapping it and also faster than any other DBAPI
I've worked with. So if you do profiling you will see that the vast majority
of time with a SQLite app is taken up by SQLAlchemy Core and ORM. Like if you
look at this profile diagram from 0.7:
http://techspot.zzzeek.org/files/2010/sqla_070b1_large.png , the proportion of
time actually spent within SQLite is that dark blue box in the center-left,
where you can see "<method 'execute' of sqlite3>" , and theres below it a
little maroon box that says "<method>", that's likely the sqlite3
cursor.fetchall() method. So all of the performance gains these pragmas get
you will at most make that one blue box a little smaller. The rest of the
screen represents time spent outside of sqlite3.
Some techniques on profiling can be seen in my stackoverflow answer here:
http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
