After I finish the current profile run I will revert and profile that one
for comparison. I am expecting another 10-15 minutes for this to finish.
On Wednesday, August 30, 2017 at 11:18:13 AM UTC-4, Simon King wrote:
>
> 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 <devil...@gmail.com
> <javascript:>> 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+...@googlegroups.com <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > 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.
