So I'm having an issue with a very slow insert, I'm inserting 223 items and it takes 20+ seconds to execute. Any advice on what I'm doing wrong and why it would be so slow? Using Postgresql 9.4.8
The line of code LOG_OUTPUT('==========PRE BULK==========', True) db_session.bulk_save_objects(trial_location_inserts) LOG_OUTPUT('==========POST BULK==========', True) And here's the log from sqlalchemy echo 2016-12-23 07:37:52.570: ==========PRE BULK========== 2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine INSERT INTO trial_locations (status, trial_id, location_id, active) VALUES (%(status)s, %(trial_id)s, %(location_id)s, %(active)s) 2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine ({'status': u'Completed', 'active': True, 'location_id': 733, 'trial_id': 126625}, {'status': u'Completed', 'active': True, 'location_id': 716, 'trial_id': 126625}, {'status': u'Completed', 'active': True, 'location_id': 1033, 'trial_id': 126625}, {'status': u'Completed', 'active': True, 'location_id': 1548, 'trial_id': 126625}, {'status': u'Completed', 'active': True, 'location_id': 1283, 'trial_id': 126625}, {'status': u'Completed', 'active': True, 'location_id': 1556, 'trial_id': 126625}, {'status': u'Completed', 'active': True, 'location_id': 4271, 'trial_id': 126625}, {'status': u'Completed', 'active': True, 'location_id': 1567, 'trial_id': 126625} ... displaying 10 of 223 total bound parameter sets ... {'status': u'Completed', 'active': True, 'location_id': 1528, 'trial_id': 126625}, {'status': u'Completed', 'active': True, 'location_id': 1529, 'trial_id': 126625}) 2016-12-23 07:38:14.270: ==========POST BULK========== Also for comparison I rewrote it in Sqlalchemy core LOG_OUTPUT('==========PRE BULK==========', True) engine.execute( TrialLocations.__table__.insert().values( trial_location_core_inserts ) ) # db_session.bulk_save_objects(trial_location_inserts) LOG_OUTPUT('==========POST BULK==========', True) and it ran in 0.028 seconds 2016-12-23 08:11:26.097: ==========PRE BULK========== ... 2016-12-23 08:11:27.025: ==========POST BULK========== I'd like to keep it in session for the sake of this being the end part of a transaction and if the whole thing fails I want it all to rollback. Am I doing something wrong? I don't need the data afterwards it just needs to be saved Help appreciated! -- 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.