Not sure how I would iterate through a non predetermined number of primary keys.
I guess part of me is wondering that although textual sql is not inherently db neutral how different between the db targets is the where field = 'value' syntax? On Wednesday, August 30, 2017 at 12:07:52 PM UTC-4, Simon King wrote: > > You could also try using executemany: > > > http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements > > > I think it would look something like this: > > table = cls.__table__ > condition = sa.and_( > table.c.pk1 == sa.bindparam('pk1'), > table.c.pk2 == sa.bindparam('pk2'), > ) > statement = sa.delete(table, whereclause=condition) > batchparams = [{'pk1': v[0], 'pk2': v[1]} for v in id_batch] > session.execute(statement, batchparams) > > Simon > > On Wed, Aug 30, 2017 at 4:28 PM, Ken MacKenzie <devil...@gmail.com > <javascript:>> wrote: > > After the current sorted profile finishes I will revert to the textual > > version and run a profile on that. I expect another 10-15 minutes for > this > > to finish right now. > > > > At present the batch size is set to 1000, total record count is just > over > > 9000 in these tests. > > > > The reason for 1000 was at first I was looking at doing this as a > > tuple_(fld, fld).in_((val, val),(val,val)) format. The 1000 should keep > me > > under most DB restrictions on the in statement. > > > > However since SQL Server does not seem to support the tuple_ usage I > > reverted to this method. > > > > I technically have one more method and that is a concat_ in_ where I > concat > > the fields. > > > > Other specifics, the table in question has 2 fields for the PK, both are > > varchar, one length 3, the other length 10. There are 5 non key fields, > 3 > > short varchars, one decimal at 14,2 precision and one varchar(800) which > > contains description text. > > > > Total record count of the table before any deletion is about 1.05 > million. > > > > Python version is 3.4.5, running on a modest CentOS desktop and to be > fair > > the SQL Server instance is sub optimal for development. > > > > 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 > > > > -- > > 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.
