And here's the objects for key, unique_new_location in unique_locations_hash.iteritems(): trial_location_inserts.append(TrialLocations(trial_id = current_trial.id, location_id = unique_new_location['location_id'], active = True, status = unique_new_location['status']))
On Friday, December 23, 2016 at 12:25:40 AM UTC-8, Brian Clark wrote: > > 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.