I have a simple query like
query = session.query(table.video_id).
join(VideoCatalog, VideoCatalog.id == table.video_id).
filter(table.event_code.in_(event_codes)).
filter(rec.video_id.in_(input_video_ids))
result = query.all()
input_video_ids is a long list of 100k values
I have observed that sqlalchemy is taking more than 2 seconds to just parse the query. This is more than the time it takes for my query to run on the database.
To make it more clear the following is taking 2 seconds to run
query = session.query(table.video_id).
join(VideoCatalog, VideoCatalog.id == table.video_id).
filter(table.event_code.in_(event_codes)).
filter(rec.video_id.in_(input_video_ids))
I tried profiling this piece of code, and I observed that the following lines consume most of the time.
4668894 function calls (4668876 primitive calls) in 2.736 seconds
Ordered by: cumulative time
ncalls tottime percall cumtime percall filename:lineno(function)
3 0.000 0.000 2.735 0.912 /usr/local/lib/python3.7/dist-packages/sqlalchemy/orm/attributes.py:218(operate)
3 0.000 0.000 2.735 0.912 /usr/local/lib/python3.7/dist-packages/sqlalchemy/orm/properties.py:366(operate)
8/2 0.000 0.000 2.735 1.367 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/operators.py:515(in_)
4 0.000 0.000 2.735 0.684 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:758(operate)
6/2 0.000 0.000 2.735 1.367 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/operators.py:1261(in_op)
4 0.000 0.000 2.735 0.684 <string>:1(<lambda>)
4 0.001 0.000 2.735 0.684 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/type_api.py:64(operate)
2 0.222 0.111 2.734 1.367 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/default_comparator.py:158(_in_impl)
179533 0.256 0.000 2.089 0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4218(_bind_param)
179533 0.764 0.000 1.833 0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:961(__init__)
179534 0.353 0.000 0.555 0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4402(__new__)
179533 0.081 0.000 0.291 0.000 /usr/lib/python3.7/re.py:185(sub)
2 0.000 0.000 0.286 0.143 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:1991(__init__)
2 0.102 0.051 0.286 0.143 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:2001(<listcomp>)
179534 0.118 0.000 0.182 0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4354(__new__)
179533 0.082 0.000 0.159 0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/type_api.py:559(coerce_compared_value)
897732 0.126 0.000 0.126 0.000 {built-in method builtins.isinstance}
179533 0.072 0.000 0.121 0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4744(_is_literal)
179537 0.033 0.000 0.119 0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4666(_expression_literal_as_text)
179533 0.082 0.000 0.108 0.000 /usr/lib/python3.7/re.py:271(_compile)
179533 0.101 0.000 0.101 0.000 {method 'sub' of 're.Pattern' objects}
179537 0.036 0.000 0.086 0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4686(_literal_as_text)
179533 0.058 0.000 0.077 0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/sqltypes.py:3011(_resolve_value_to_type)
179537 0.065 0.000 0.065 0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:706(self_group)
179537 0.029 0.000 0.050 0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4670(_literal_as)
179541 0.042 0.000 0.042 0.000 {built-in method __new__ of type object at 0xa21aa0}
179555 0.021 0.000 0.021 0.000 {built-in method builtins.getattr}
179544 0.020 0.000 0.020 0.000 {built-in method builtins.hasattr}
179549 0.019 0.000 0.019 0.000 {method 'get' of 'dict' objects}
179533 0.019 0.000 0.019 0.000 {built-in method builtins.id}
179533 0.018 0.000 0.018 0.000 {method 'strip' of 'str' objects}
179538 0.016 0.000 0.016 0.000 {method 'append' of 'list' objects}
I also tried preparing the raw query and issuing an session.execute(Raw Query). It doesn't consume this extra 2 seconds. That is why I suspect it has to something with sqlalchemy's preprocessing.
Sqlalchemy version = 1.3.17
Is there any way to optimize this? Any help is highly appreciated.