I am noticing the 16 execute calls. There should only be 10 with a batch size of 1000 and a record size of 9,000 and some change.
Are you just wanting to see the sql via a print(q)? On Wednesday, August 30, 2017 at 2:01:31 PM UTC-4, Mike Bayer wrote: > > it looks like you have 16 SQL statements that take a very different > amount of time depending on format. Can you please paste the output > with echo=True on create_engine() so that the difference in SQL > statements can be seen? > > On Wed, Aug 30, 2017 at 12:30 PM, Ken MacKenzie <devil...@gmail.com > <javascript:>> wrote: > > 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> > 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. > >> > To post to this group, send email to sqlal...@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+...@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.
