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.