Thanks Mike as always, I'm diving a bit more in the problem. The solution they decided to apply is to modify the code from above, like this:
header = 'INSERT INTO `passageData` (`time`, `azimuth`, ' \ '`elevation`, `doppler`, `slant`, `passageID`) VALUES ' with profiled(): for ix in range(n): time_i = times[ix].strftime("%Y-%m-%d %H:%M:%S.%f")[:-3] az_i = round(azimuth[ix], 2) el_i = round(elevation[ix], 2) dopp_i = round(doppler[ix], 10) slant_i = round(slant[ix], 2) header += '(\'{0}\', {1}, {2}, {3}, {4}, {5})'.\ format(time_i, az_i, el_i, dopp_i, slant_i, self.id) if ix != n-1: header += ',' header += ';' session.execute(header) And I'm profiling the previous one with: with profiled(): session.execute( insert_query, [ { 'time': times[i], 'elevation': elevation[i], 'azimuth': azimuth[i], 'doppler': doppler[i], 'slant': slant[i], 'passageID': passage_id } for i in six.moves.range(0, n) ] ) And these are the lines I get from the profiling in the first case (with ugly string interpolation): 420358 function calls in 1.591 seconds Ordered by: cumulative time ncalls tottime percall cumtime percall filename:lineno(function) 1 0.000 0.000 1.114 1.114 /lib/python2.7/site-packages/sqlalchemy/orm/session.py:1047(execute) 1 0.000 0.000 0.971 0.971 /lib/python2.7/site-packages/sqlalchemy/engine/base.py:882(execute) 1 0.000 0.000 0.971 0.971 /lib/python2.7/site-packages/sqlalchemy/sql/elements.py:267(_execute_on_connection) 1 0.000 0.000 0.971 0.971 /lib/python2.7/site-packages/sqlalchemy/engine/base.py:1016(_execute_clauseelement) 1 0.000 0.000 0.834 0.834 /lib/python2.7/site-packages/sqlalchemy/engine/base.py:1111(_execute_context) 1 0.000 0.000 0.789 0.789 /lib/python2.7/site-packages/sqlalchemy/engine/default.py:508(do_execute) 1 0.005 0.005 0.789 0.789 /lib/python2.7/site-packages/MySQLdb/cursors.py:204(execute) 1 0.000 0.000 0.780 0.780 /lib/python2.7/site-packages/MySQLdb/cursors.py:411(_query) 1 0.000 0.000 0.780 0.780 /lib/python2.7/site-packages/MySQLdb/cursors.py:372(_do_query) 1 0.780 0.780 0.780 0.780 /lib/python2.7/site-packages/MySQLdb/connections.py:267(query) 3 0.267 0.089 0.267 0.089 {method 'sub' of '_sre.SRE_Pattern' objects} 60000 0.205 0.000 0.205 0.000 {method 'strftime' of 'datetime.date' objects} 1 0.011 0.011 0.143 0.143 /lib/python2.7/site-packages/sqlalchemy/sql/elements.py:4287(_literal_as_text) 1 0.000 0.000 0.137 0.137 <string>:1(<lambda>) 1 0.000 0.000 0.137 0.137 /lib/python2.7/site-packages/sqlalchemy/sql/elements.py:379(compile) 1 0.000 0.000 0.137 0.137 /lib/python2.7/site-packages/sqlalchemy/sql/elements.py:444(_compiler) 1 0.000 0.000 0.137 0.137 /lib/python2.7/site-packages/sqlalchemy/sql/compiler.py:393(__init__) 1 0.000 0.000 0.137 0.137 /lib/python2.7/site-packages/sqlalchemy/sql/compiler.py:180(__init__) 1 0.000 0.000 0.137 0.137 /lib/python2.7/site-packages/sqlalchemy/sql/compiler.py:244(process) 1 0.000 0.000 0.137 0.137 /lib/python2.7/site-packages/sqlalchemy/sql/visitors.py:86(_compiler_dispatch) 1 0.000 0.000 0.137 0.137 /lib/python2.7/site-packages/sqlalchemy/sql/compiler.py:759(visit_textclause) 1 0.000 0.000 0.132 0.132 /lib/python2.7/site-packages/sqlalchemy/sql/elements.py:1259(__init__) 240000 0.120 0.000 0.120 0.000 {round} 60000 0.104 0.000 0.104 0.000 {method 'format' of 'str' objects} 60000 0.047 0.000 0.047 0.000 /lib/python2.7/site-packages/sqlalchemy/orm/attributes.py:234(__get__) While in the second case I get: 5103248 function calls (5103183 primitive calls) in 3.829 seconds Ordered by: cumulative time ncalls tottime percall cumtime percall filename:lineno(function) 1 0.000 0.000 3.829 3.829 lib/python2.7/site-packages/sqlalchemy/orm/session.py:1047(execute) 1 0.000 0.000 3.829 3.829 lib/python2.7/site-packages/sqlalchemy/engine/base.py:882(execute) 1 0.000 0.000 3.829 3.829 lib/python2.7/site-packages/sqlalchemy/sql/elements.py:267(_execute_on_connection) 1 0.000 0.000 3.829 3.829 lib/python2.7/site-packages/sqlalchemy/engine/base.py:1016(_execute_clauseelement) 1 0.001 0.001 3.829 3.829 lib/python2.7/site-packages/sqlalchemy/engine/base.py:1111(_execute_context) 1 0.000 0.000 2.829 2.829 lib/python2.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py:104(do_executemany) 1 0.000 0.000 2.829 2.829 lib/python2.7/site-packages/MySQLdb/cursors.py:256(executemany) 1 0.130 0.130 2.829 2.829 lib/python2.7/site-packages/MySQLdb/cursors.py:286(_do_execute_many) 60000 0.198 0.000 1.970 0.000 lib/python2.7/site-packages/MySQLdb/cursors.py:105(_escape_args) 420000 0.169 0.000 1.245 0.000 lib/python2.7/site-packages/MySQLdb/cursors.py:111(<genexpr>) 360000 0.378 0.000 1.076 0.000 lib/python2.7/site-packages/MySQLdb/connections.py:302(literal) 1 0.368 0.368 0.998 0.998 lib/python2.7/site-packages/sqlalchemy/engine/default.py:595(_init_compiled) 61 0.001 0.000 0.703 0.012 lib/python2.7/site-packages/MySQLdb/cursors.py:204(execute) 61 0.000 0.000 0.702 0.012 lib/python2.7/site-packages/MySQLdb/cursors.py:411(_query) 61 0.001 0.000 0.701 0.011 lib/python2.7/site-packages/MySQLdb/cursors.py:372(_do_query) 61 0.699 0.011 0.699 0.011 lib/python2.7/site-packages/MySQLdb/connections.py:267(query) 360000 0.160 0.000 0.543 0.000 {method 'escape' of '_mysql.connection' objects} 60000 0.181 0.000 0.521 0.000 {map} 360000 0.191 0.000 0.339 0.000 lib/python2.7/site-packages/MySQLdb/cursors.py:98(_ensure_bytes) 1560325 0.323 0.000 0.323 0.000 {isinstance} 60000 0.313 0.000 0.313 0.000 lib/python2.7/site-packages/sqlalchemy/sql/compiler.py:526(construct_params) 240000 0.076 0.000 0.284 0.000 lib/python2.7/site-packages/sqlalchemy/sql/type_api.py:1181(process) 240000 0.085 0.000 0.208 0.000 /Users/rubendibattista/git/Leaf/pyggdrasill/pyggdrasill/sql/types/small_float.py:10(process_bind_param) 60000 0.034 0.000 0.206 0.000 lib/python2.7/site-packages/MySQLdb/times.py:125(DateTime2literal) 240000 0.155 0.000 0.155 0.000 lib/python2.7/site-packages/MySQLdb/converters.py:68(Float2Str) 60000 0.028 0.000 0.154 0.000 lib/python2.7/site-packages/MySQLdb/times.py:39(format_TIMESTAMP) 60000 0.126 0.000 0.126 0.000 {method 'format' of 'str' objects} 240000 0.123 0.000 0.123 0.000 {round} 420015 0.033 0.000 0.033 0.000 {method 'append' of 'list' objects} 60000 0.022 0.000 0.022 0.000 lib/python2.7/site-packages/MySQLdb/converters.py:58(Thing2Str) 60000 0.018 0.000 0.018 0.000 {_mysql.string_literal} 180206 0.014 0.000 0.014 0.000 {len} 61 0.001 0.000 0.002 0.000 lib/python2.7/site-packages/MySQLdb/cursors.py:182(_do_get_result) So I get a way bigger number of function calls. To me this seems related to the args escaping from MySQLdb cursors.py. Is there any better way to optimize that INSERT query? On Friday, November 2, 2018 at 5:23:51 PM UTC+1, Mike Bayer wrote: > > On Fri, Nov 2, 2018 at 11:17 AM Ruben Di Battista > <rubendi...@gmail.com <javascript:>> wrote: > > > > Hello, > > > > I have a huge insert of the type: > > > > ``` > > session.execute( > > insert_query, > > [ > > { > > 'time': times[i], > > 'elevation': elevation[i], > > 'azimuth': azimuth[i], > > 'doppler': doppler[i], > > 'slant': slant[i], > > 'passageID': passage_id > > } > > for i in six.moves.range(0, n) > > ] > > ) > > > > ``` > > > > where n is huge. > > > > I was told by a colleague that SQLALchemy limits query length to 65536 > chars while MySQL can cappet 33MB big queries. Can I tailor this in > SQLAlchemy? > > your colleague is mistaken. SQLAlchemy has no limit on the sizes of > query strings or any other string. > > > > > > -- > > 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.