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.
