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