It would be interesting to see the profile of the textual SQL version.
It looks like most of the time is being spent inside pyodbc, rather
than SQLAlchemy, so I guess it must be something to do with the
processing of bind parameters. How many parameters are being sent in
per query? ie. what is len(id_batch) * len(cls.SQL_PK)?
You could try playing with your batch sizes to see what sort of effect that has.
Simon
On Wed, Aug 30, 2017 at 4:01 PM, Ken MacKenzie <deviloc...@gmail.com> wrote:
> So I implemented this version with one change. I moved this line:
>
> cols = [getattr(cls, colname) for colname in cls.SQL_PK]
>
> To the beginning before the loop, actually before the outer loop that parses
> the batch.
>
> However, the new version is MUCH slower than the original, so I profiled it
> and this is what I get:
>
> %prun import db_api_lib.del_test
> 1421296 function calls (1277536 primitive calls) in 620.434 seconds
>
> Ordered by: internal time
>
> ncalls tottime percall cumtime percall filename:lineno(function)
> 12 615.512 51.293 615.512 51.293 {method 'execute' of
> 'pyodbc.Cursor' objects}
> 208876/208271 0.316 0.000 0.332 0.000 {built-in method
> isinstance}
> 96876/96874 0.184 0.000 0.185 0.000 {built-in method getattr}
> 11123 0.137 0.000 0.242 0.000 elements.py:2927(__init__)
> 38970/10 0.136 0.000 1.502 0.150
> visitors.py:75(_compiler_dispatch)
> 11123 0.128 0.000 0.397 0.000 elements.py:861(__init__)
> 44492/11123 0.127 0.000 1.504 0.000 operators.py:297(__eq__)
> 5568 0.116 0.000 0.617 0.000 elements.py:1860(_construct)
> 11132 0.113 0.000 0.246 0.000 compiler.py:676(visit_column)
> 11123 0.111 0.000 1.039 0.000
> compiler.py:1040(_generate_generic_binary)
> 5567 0.108 0.000 0.108 0.000 {method 'fetchone' of
> 'pyodbc.Cursor' objects}
> 11123 0.099 0.000 0.646 0.000
> default_comparator.py:290(_check_literal)
> 16684 0.095 0.000 0.195 0.000
> operators.py:1007(is_precedent)
> 11123 0.094 0.000 1.010 0.000
> default_comparator.py:22(_boolean_compare)
> 33375/11129 0.090 0.000 1.445 0.000 {built-in method eq}
> 11123 0.075 0.000 0.465 0.000
> compiler.py:1164(visit_bindparam)
> 1 0.075 0.075 619.467 619.467 db_base.py:138(dict_delete)
> 11139 0.073 0.000 0.331 0.000 base.py:1128(decorate)
> 68831 0.070 0.000 0.070 0.000 {method 'get' of 'dict'
> objects}
> 1 0.068 0.068 620.471 620.471 del_test.py:1(<module>)
> 22250/5567 0.067 0.000 1.482 0.000 compiler.py:806(<genexpr>)
> 11125 0.066 0.000 0.102 0.000
> compiler.py:1265(_process_anon)
> 11123 0.065 0.000 1.179 0.000 compiler.py:1006(visit_binary)
> 11123 0.063 0.000 1.263 0.000 base.py:1306(visit_binary)
> 11123 0.061 0.000 0.458 0.000 elements.py:3818(_bind_param)
> 22269 0.058 0.000 0.097 0.000 compiler.py:2901(quote)
> 11125 0.058 0.000 0.261 0.000
> compiler.py:1246(_truncated_identifier)
> 11123 0.058 0.000 1.299 0.000 annotation.py:100(__eq__)
> 11123 0.056 0.000 1.066 0.000 type_api.py:60(operate)
> 56386 0.055 0.000 0.055 0.000 {method 'append' of 'list'
> objects}
> 11126 0.055 0.000 0.168 0.000 elements.py:3962(__new__)
> 11123 0.055 0.000 0.334 0.000
> compiler.py:1233(_truncate_bindparam)
> 11125 0.054 0.000 0.192 0.000 elements.py:4073(apply_map)
> 11206 0.051 0.000 0.089 0.000 elements.py:3918(__new__)
> 22246 0.051 0.000 0.051 0.000 elements.py:640(self_group)
> 22250/5567 0.049 0.000 1.493 0.000 compiler.py:804(<genexpr>)
> 2 0.048 0.024 0.049 0.024 {built-in method connect}
> 11123 0.047 0.000 1.386 0.000 properties.py:269(operate)
> 8689/3128 0.045 0.000 1.508 0.000 {method 'join' of 'str'
> objects}
> 11123 0.045 0.000 0.076 0.000
> compiler.py:959(_get_operator_dispatch)
> 11123 0.045 0.000 0.056 0.000
> compiler.py:1271(bindparam_string)
> 11123 0.040 0.000 0.411 0.000
> annotation.py:78(_compiler_dispatch)
> 15610/15322 0.038 0.000 0.043 0.000 {built-in method hasattr}
> 5567 0.038 0.000 0.294 0.000 elements.py:1883(<listcomp>)
> 11125 0.036 0.000 0.138 0.000
> _collections.py:728(__missing__)
> 16691 0.035 0.000 0.090 0.000
> elements.py:4216(_expression_literal_as_text)
> 5561 0.035 0.000 1.539 0.000 db_base.py:174(<listcomp>)
> 16704 0.033 0.000 0.055 0.000
> elements.py:4220(_literal_as_text)
> 11123 0.033 0.000 0.073 0.000
> type_api.py:504(coerce_compared_value)
> 31584/31469 0.031 0.000 0.032 0.000 {built-in method len}
> 11123 0.030 0.000 0.165 0.000 elements.py:2986(self_group)
> 5562 0.030 0.000 0.187 0.000 result.py:1156(fetchone)
> 11131 0.030 0.000 0.077 0.000 compiler.py:494(<genexpr>)
> 11123 0.029 0.000 0.047 0.000
> type_api.py:452(_cached_bind_processor)
>
> Any guidance to how I can improve the performance of this solution and
> remain DB neutral?
>
> On Wednesday, August 30, 2017 at 4:43:01 AM UTC-4, Simon King wrote:
>>
>> On Tue, Aug 29, 2017 at 9:49 PM, Ken MacKenzie <devil...@gmail.com> wrote:
>> > I have a query I have constructed and I had to deal with a composite
>> > primary
>> > key to select items;
>> >
>> > q = s.query(cls)
>> > or_cond = []
>> > for x in id_batch:
>> >
>> > pkf = [text(f + "=='" + v + "'") for f,v in
>> > zip(cls.SQL_PK,
>> > x)]
>> > and_cond = (and_(*pkf))
>> > or_cond.append(and_cond)
>> >
>> >
>> > q =
>> > q.filter(or_(*or_cond)).delete(synchronize_session=False)
>> >
>> > cls.SQL_PK is a tuple of the primary key fields for the model described
>> > by
>> > class. This is a class method that is part of a inherited class to the
>> > model
>> >
>> > The current target is SQL Server. My concern is using text('field =
>> > 'value'), is that going to work for other DB targets like say postgres?
>> >
>> > The first round of doing this I tried using a
>> > tuple_(*cls.SQL_PK).in_(id_batch), but that did not work and the
>> > resulting
>> > SQL id not work in SSMS leading me to believe that SQL Server (or at
>> > least
>> > the version we are using) does not support tuples.
>> >
>>
>> Textual SQL is not DB-neutral in general. Luckily, in this case you
>> shouldn't even need it. Try something like this:
>>
>> for x in id_batch:
>> cols = [getattr(cls, colname) for colname in cls.SQL_PK]
>> pkf = [(col == v) for (col, v) in zip(cols, x)]
>> and_cond = and_(*pkf)
>> or_cond.append(and_cond)
>>
>> ie. use "getattr" to retrieve the actual column property from the class.
>>
>> Hope that helps,
>>
>> Simon
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
