StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There's an extremely useful profiling recipe on the <a href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Profiling" rel="noreferrer">SQLAlchemy wiki</a></p> <p>With a couple of minor modifications,</p> <pre><code>from sqlalchemy import event from sqlalchemy.engine import Engine import time import logging logging.basicConfig() logger = logging.getLogger("myapp.sqltime") logger.setLevel(logging.DEBUG) @event.listens_for(Engine, "before_cursor_execute") def before_cursor_execute(conn, cursor, statement, parameters, context, executemany): context._query_start_time = time.time() logger.debug("Start Query:\n%s" % statement) # Modification for StackOverflow answer: # Show parameters, which might be too verbose, depending on usage.. logger.debug("Parameters:\n%r" % (parameters,)) @event.listens_for(Engine, "after_cursor_execute") def after_cursor_execute(conn, cursor, statement, parameters, context, executemany): total = time.time() - context._query_start_time logger.debug("Query Complete!") # Modification for StackOverflow: times in milliseconds logger.debug("Total Time: %.02fms" % (total*1000)) if __name__ == '__main__': from sqlalchemy import * engine = create_engine('sqlite://') m1 = MetaData(engine) t1 = Table("sometable", m1, Column("id", Integer, primary_key=True), Column("data", String(255), nullable=False), ) conn = engine.connect() m1.create_all(conn) conn.execute( t1.insert(), [{"data":"entry %d" % x} for x in xrange(100000)] ) conn.execute( t1.select().where(t1.c.data.between("entry 25", "entry 7800")).order_by(desc(t1.c.data)) ) </code></pre> <p>Output is something like:</p> <pre><code>DEBUG:myapp.sqltime:Start Query: SELECT sometable.id, sometable.data FROM sometable WHERE sometable.data BETWEEN ? AND ? ORDER BY sometable.data DESC DEBUG:myapp.sqltime:Parameters: ('entry 25', 'entry 7800') DEBUG:myapp.sqltime:Query Complete! DEBUG:myapp.sqltime:Total Time: 410.46ms </code></pre> <p>Then if you find an oddly slow query, you could take the query string, format in the parameters (can be done the <code>%</code> string-formatting operator, for psycopg2 at least), prefix it with "EXPLAIN ANALYZE" and shove the query plan output into <a href="http://explain.depesz.com/" rel="noreferrer">http://explain.depesz.com/</a> (found via <a href="http://robots.thoughtbot.com/post/2638538135/postgresql-performance-considerations" rel="noreferrer">this good article on PostgreSQL performance</a>)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload