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 <[email protected]> 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
