Re: [sqlalchemy] how to prepare get by primary key query?
On Wed, Apr 3, 2013 at 5:47 PM, Michael Bayer mike...@zzzcomputing.comwrote: the Python DBAPI doesn't have an explicit concept of a prepared statement - the idea of preparing a statement involves that a statement handle is established on the database server, which can then be reused. The closest the DBAPI has is the executemany() call, which gives the DBAPI itself the option of using a prepared statement behind the scenes; but this option is not appropriate for SELECT statements since executemany() doesn't support the return of results. This is what I was looking for. It looks like pysqlite does it internally. On the Python SQL construction side, SQLAlchemy deals with expression constructs that are converted to strings given a database dialect as well as compilation options. The amount of time in Python spent for this compilation is relatively small and has been optimized for many years to be as fast as possible. Nevertheless, in some cases we do try to squeeze more performance out by caching the compiled form of these queries; the ORM in particular will cache the compiled form of insert/update/delete statements that are used by the unit of work. Right now, the string form of SELECT queries generated by the ORM are not automatically cached. It's only worthwhile to try to cache queries that are fixed, such as the get() query we're referring to here as well as some of the queries used by lazyloading of relationships. The overhead of this compilation however is fairly minimal; reddit.com uses SQLAlchemy expression constructs for all database queries as well, and they serve well over two billion page views a month without any caching of the expression string. In my case the performance boost was 30%. I have very few queries, but pretty big loops, so compiling them in advance(outside of loop) gave me a significant gain. There's a recipe to make use of the compiled_cache in conjunction with the Query object right now, which is at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/BakedQuery . Thank you! -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] how to prepare get by primary key query?
On Apr 4, 2013, at 2:03 PM, Roman Yakovenko roman.yakove...@gmail.com wrote: In my case the performance boost was 30%. I have very few queries, but pretty big loops, so compiling them in advance(outside of loop) gave me a significant gain. OK well we have the architecture in place to cache SQL expression constructs against their compiled form in the ORM on a per-mapper basis, using an LRU cache. The SQL emitted by get() is in most cases constant so this is a feature that can be added without too much difficulty. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] how to prepare get by primary key query?
On Apr 3, 2013, at 1:01 AM, Roman Yakovenko roman.yakove...@gmail.com wrote:
Hello.
I am just starting with SQLAlchemy and have a small problem: my get query
is compiled every time I use it.
According to cProfile:
ncalls tottime percall cumtime percall filename:lineno(function)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py:1864(compile)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py:1908(_compiler)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py:231(__init__)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/engine/interfaces.py:764(__init__)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/engine/interfaces.py:805(process)
90/100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/visitors.py:73(_compiler_dispatch)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py:1082(visit_select)
In case of native API, I would use prepare command/statement, but it looks
I am missing something obvious and cannot find similar functionality in the
package.
the Python DBAPI doesn't have an explicit concept of a prepared statement -
the idea of preparing a statement involves that a statement handle is
established on the database server, which can then be reused. The closest the
DBAPI has is the executemany() call, which gives the DBAPI itself the option
of using a prepared statement behind the scenes; but this option is not
appropriate for SELECT statements since executemany() doesn't support the
return of results.
On the Python SQL construction side, SQLAlchemy deals with expression
constructs that are converted to strings given a database dialect as well as
compilation options. The amount of time in Python spent for this compilation
is relatively small and has been optimized for many years to be as fast as
possible. Nevertheless, in some cases we do try to squeeze more performance
out by caching the compiled form of these queries; the ORM in particular will
cache the compiled form of insert/update/delete statements that are used by the
unit of work.
Right now, the string form of SELECT queries generated by the ORM are not
automatically cached. It's only worthwhile to try to cache queries that are
fixed, such as the get() query we're referring to here as well as some of the
queries used by lazyloading of relationships. The overhead of this compilation
however is fairly minimal; reddit.com uses SQLAlchemy expression constructs for
all database queries as well, and they serve well over two billion page views a
month without any caching of the expression string.
There's a recipe to make use of the compiled_cache in conjunction with the
Query object right now, which is at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/BakedQuery .
Below, you can find a complete source code, which reproduce the issue.
Thank you!
import sqlalchemy
import sqlalchemy.ext.declarative
Base = sqlalchemy.ext.declarative.declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
name = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
def run_query(session):
employee_by_id = session.query(Employee)
for i in range(10):
x = employee_by_id.get(i)
print x.name
session.rollback()
if __name__ == '__main__':
engine = sqlalchemy.create_engine('sqlite://')
Base.metadata.create_all(engine)
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
for i in range(10):
session.add(Employee(id=i, name='name' + str(i)))
session.commit()
import cProfile
cProfile.run('run_query(Session())', 'get.stats')
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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] how to prepare get by primary key query?
Hello.
I am just starting with SQLAlchemy and have a small problem: my get query
is compiled every time I use it.
According to cProfile:
ncalls tottime percall cumtime percall filename:lineno(function)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py:1864(compile)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py:1908(_compiler)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py:231(__init__)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/engine/interfaces.py:764(__init__)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/engine/interfaces.py:805(process)
90/100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/visitors.py:73(_compiler_dispatch)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py:1082(visit_select)
In case of native API, I would use prepare command/statement, but it
looks I am missing something obvious and cannot find similar functionality
in the package.
Below, you can find a complete source code, which reproduce the issue.
Thank you!
import sqlalchemy
import sqlalchemy.ext.declarative
Base = sqlalchemy.ext.declarative.declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
name = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
def run_query(session):
employee_by_id = session.query(Employee)
for i in range(10):
x = employee_by_id.get(i)
print x.name
session.rollback()
if __name__ == '__main__':
engine = sqlalchemy.create_engine('sqlite://')
Base.metadata.create_all(engine)
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
for i in range(10):
session.add(Employee(id=i, name='name' + str(i)))
session.commit()
import cProfile
cProfile.run('run_query(Session())', 'get.stats')
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
