mysqlsqlalchemylatency

Understand what's underhood SQLAlchemy query and its latency

Here's my pseudo code for SQL query using SQLAlchemy:

from sqlalchemy.orm import query
query = MyClass.query

if arg1:
   query = query.filter(MyClass.f1 == arg1)

if arg2:
   query = query.filter(MyClass.f2 == arg2)

if arg3:
   query = query.filter(MyClass.f3 == arg3)

res = jsonify(requests=[r.to_dict() for r in query])

return res

I calculated the time duration for each of step in the code, and surprised to find query.filter is quite fast (~0.0001s) while rate-limiting step is jsonify(requests=[r.to_dict() for r in query])(~0.3s !) How to explain this?

Also here's my log:

f1 time 0.0002
f2 time 0.0001
2023-05-04 09:37:09,667 INFO sqlalchemy.engine.base.Engine SELECT 1
INFO:sqlalchemy.engine.base.Engine:SELECT 1
2023-05-04 09:37:09,667 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
2023-05-04 09:37:09,668 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2023-05-04 09:37:09,669 INFO sqlalchemy.engine.base.Engine SELECT ...
...
...
jsonify time 0.31
...

From the log traces, seems all real SQL operation seems done after query.filter? Thanks

Solution

  • SQLAlchemy methods like filter(), group_by(), etc. don't execute the query. They construct a query object that contains an internal representation of the query -- what tables are being queried, the columns to return, the table relationships. Each time you call one of these functions (often they're chained to build up the desired query), it returns a new query object with the specified changes.

    The query isn't executed until you call a method that returns results, such as to_dict(). At this time, the internal representation is converted to SQL, which is sent to the database. The results are then collected into the dictionary.

    So .filter() doesn't tell SQLAlchemy to filter the results itself, it just tells it that when it creates the query it should add appropriate WHERE conditions that make the database do the requested filtering.