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 [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.
