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 <deviloc...@gmail.com> 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+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. -- 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.
