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 <[email protected]
> <javascript:>> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
