When profiling an application, I've found out that SQLAlchemy compiling SQL is one of tasks that takes most of CPU time of the app. I've already made bound parameters explicit, but it appears that SQLAlchemy only compiles the query when it's time to execute it.
Is there a way to make it pre-compile the query so that only minimal work is required to replace the bound parameters before executing it?
There is a way... kind of. I was already defining the query at module parsing time:
class MyClass(object):
# This works (slow)
query = select(...).where(...).order_by('distance').limit(1)
...
@classmethod
def issue_query(cls, **params):
cls.engine.execute(cls.query, params)
But SQLAlchemy kept only compiling MyClass.query only when MyClass.issue_query was called (every time it was called). I've noticed that defining the query as a text() made it pretty fast (120us vs 600us)... but str(query) compiles the query into pretty much a text() argument, so I've tried defining it as:
class MyClass(object):
# This doesn't work
query = text(str(select(...).where(...).order_by('distance').limit(1)))
...
@classmethod
def issue_query(cls, **params):
cls.engine.execute(cls.query, params)
But SQLAlchemy tries to make sense of the 'distance' inside order_by() and converts the 1 inside limit() to a bound parameter (that is, removes the value from the query). This can be solved with a little hack:
class MyClass(object):
# This works (fast)
query = text(str(select(...).where(...).order_by(text('distance')).limit(text('1'))))
...
@classmethod
def issue_query(cls, **params):
cls.engine.execute(cls.query, params)