I tried a batch size as small as ten and each step took about 2 seconds. I set
it up to show me the time between each batch for that run. Lowering the batch
size actually seems to worsen performance. Which is not what I expected.
Sent from my iPad
> On Aug 31, 2017, at 5:42 AM, Simon King <si...@simonking.org.uk> wrote:
>
> I'm pretty sure the bad performance is due to pyodbc (or maybe SQL
> Server) not handling *huge* numbers of bind parameters gracefully. You
> are generating a query with (batch_size * n_primary_key_columns) bind
> parameters, which even in your original version meant 2000 bind
> parameters.
>
> Try with a batch sizes of 10, 50 and 100. You'll run more queries but
> I suspect each query will perform better.
>
> Simon
>
>> On Wed, Aug 30, 2017 at 10:02 PM, Ken MacKenzie <deviloc...@gmail.com> wrote:
>> With a batch size of 10000 based on the original selection, no improvement.
>>
>> I am going to see what sql this is creating.
>>
>> 2328928 function calls (2085124 primitive calls) in 1084.925
>> seconds
>>
>> Ordered by: internal time
>>
>> ncalls tottime percall cumtime percall filename:lineno(function)
>> 26 1074.733 41.336 1074.733 41.336 {method 'execute' of
>> 'pyodbc.Cursor' objects}
>> 2 0.871 0.436 0.872 0.436 {built-in method connect}
>> 349128/348523 0.556 0.000 0.573 0.000 {built-in method
>> isinstance}
>> 26 0.502 0.019 0.502 0.019 {method 'close' of
>> 'pyodbc.Cursor' objects}
>> 20 0.485 0.024 0.485 0.024 {method 'commit' of
>> 'pyodbc.Connection' objects}
>> 161062/161060 0.317 0.000 0.318 0.000 {built-in method getattr}
>> 19123 0.259 0.000 0.789 0.000 elements.py:861(__init__)
>> 9567 0.258 0.000 0.258 0.000 {method 'fetchone' of
>> 'pyodbc.Cursor' objects}
>> 76492/19123 0.248 0.000 2.842 0.000 operators.py:297(__eq__)
>> 67012/24 0.236 0.000 2.601 0.108
>> visitors.py:75(_compiler_dispatch)
>> 19123 0.234 0.000 0.424 0.000 elements.py:2927(__init__)
>> 9582 0.213 0.000 1.104 0.000 elements.py:1860(_construct)
>> 19132 0.205 0.000 0.435 0.000 compiler.py:676(visit_column)
>> 19123 0.189 0.000 1.806 0.000
>> compiler.py:1040(_generate_generic_binary)
>> 19123 0.183 0.000 1.263 0.000
>> default_comparator.py:290(_check_literal)
>> 57389/19143 0.173 0.000 2.732 0.000 {built-in method eq}
>> 19123 0.171 0.000 1.912 0.000
>> default_comparator.py:22(_boolean_compare)
>> 28684 0.169 0.000 0.346 0.000
>> operators.py:1007(is_precedent)
>> 19126 0.143 0.000 0.352 0.000 elements.py:3962(__new__)
>> 19123 0.129 0.000 0.795 0.000
>> compiler.py:1164(visit_bindparam)
>> 19123 0.128 0.000 0.918 0.000 elements.py:3818(_bind_param)
>> 19153 0.126 0.000 0.581 0.000 base.py:1128(decorate)
>> 1 0.125 0.125 1084.925 1084.925 del_test.py:1(<module>)
>> 116845 0.121 0.000 0.121 0.000 {method 'get' of 'dict'
>> objects}
>> 38264/9581 0.115 0.000 2.567 0.000 compiler.py:806(<genexpr>)
>> 19123 0.113 0.000 2.048 0.000 compiler.py:1006(visit_binary)
>> 19125 0.113 0.000 0.174 0.000
>> compiler.py:1265(_process_anon)
>> 19125 0.109 0.000 0.451 0.000
>> compiler.py:1246(_truncated_identifier)
>> 19123 0.108 0.000 2.457 0.000 annotation.py:100(__eq__)
>> 19123 0.107 0.000 2.190 0.000 base.py:1306(visit_binary)
>> 19123 0.104 0.000 2.015 0.000 type_api.py:60(operate)
>> 38283 0.101 0.000 0.167 0.000 compiler.py:2901(quote)
>> 19123 0.095 0.000 0.577 0.000
>> compiler.py:1233(_truncate_bindparam)
>> 19206 0.094 0.000 0.163 0.000 elements.py:3918(__new__)
>> 19125 0.093 0.000 0.323 0.000 elements.py:4073(apply_map)
>> 38246 0.091 0.000 0.091 0.000 elements.py:640(self_group)
>> 88460 0.088 0.000 0.088 0.000 {method 'append' of 'list'
>> objects}
>> 19123 0.084 0.000 2.621 0.000 properties.py:269(operate)
>> 38264/9581 0.084 0.000 2.586 0.000 compiler.py:804(<genexpr>)
>> 19123 0.082 0.000 0.732 0.000
>> annotation.py:78(_compiler_dispatch)
>> 12703/3142 0.076 0.000 2.605 0.001 {method 'join' of 'str'
>> objects}
>> 19123 0.075 0.000 0.129 0.000
>> compiler.py:959(_get_operator_dispatch)
>> 1 0.071 0.071 1082.250 1082.250 db_base.py:172(dict_delete)
>> 19123 0.070 0.000 0.089 0.000
>> compiler.py:1271(bindparam_string)
>> 19123 0.070 0.000 2.085 0.000 <string>:1(<lambda>)
>> 9581 0.067 0.000 0.520 0.000 elements.py:1883(<listcomp>)
>> 9561 0.064 0.000 2.905 0.000 db_base.py:208(<listcomp>)
>> 23611/23323 0.062 0.000 0.067 0.000 {built-in method hasattr}
>> 28705 0.061 0.000 0.159 0.000
>> elements.py:4216(_expression_literal_as_text)
>> 28746 0.058 0.000 0.098 0.000
>> elements.py:4220(_literal_as_text)
>> 19123 0.058 0.000 0.130 0.000
>> type_api.py:504(coerce_compared_value)
>> 9562 0.056 0.000 0.416 0.000 result.py:1156(fetchone)
>> 19125 0.056 0.000 0.230 0.000
>> _collections.py:728(__missing__)
>> 19123 0.054 0.000 0.296 0.000 elements.py:2986(self_group)
>>
>>
>>
>> --
>> 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 a topic in the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/lAT3Epi7Fkw/unsubscribe.
> To unsubscribe from this group and all its topics, 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.
