On May 28, 2012, at 10:52 AM, Jeff wrote:
> There are Analyses, which have Events. There's an
> Analysis_Event_Association table. Events are written before the
> Analyses. Later, I have a for loop calculating multiple Analyses. For
> each Analysis, we identify the Events it includes and append them,
> using sqlalchemy's relationship plumbing. This just writes the
> appropriate row to the association table. It works great:
>
> all_events =
> list(session.query(Event).filter(....).order_by(...).all())
>
> for a in analyses_parameters:
> analysis = Analysis(....)
> events_in_this_analysis = all_events[...]
> analysis.events = events_in_this_analysis
> session.append(analysis)
>
> session.commit()
> session.close()
> session.bind.dispose()
>
> The issue is that I'm in a situation where there are many, many
> processes writing to the same database, and the database only allows
> us a limited number of connections. So I'm trying to have connections
> open only when they're actually being used, hence the
> session.bind.dispose().
engine.dispose() is an inefficient operation, as the Engine is a factory for
connections, not a connection itself. If you'd like there to be zero actual
database connections open when the application is idle, you can disable pooling
using NullPool.
>
> Writing all the analyses is a single insert, and thus is efficient
> with regards to having a connection open. HOWEVER, it appears that
> each of the Events associations we appended are being inserted
> individually, which is bad. The connection sits there, then is used,
> then sits there, then is used, etc. This contributes to the maximum
> connections open, which is a limited resource.
Just to make sure we're on the same page, the Session procures a single DBAPI
connection, and uses it for the lifespan of that transaction. So the above
code is using only one connection until COMMIT is called. Unless there is a
significant period of latency in the above loop, it would be way less efficient
and also defeat transactional guarantees to use a new connection for each
INSERT. If your app is using MyISAM tables then the transactional guarantees
are moot in any case, though. But breaking up a tight loop to repeatedly
connect over and over again for each record being inserted, in the effort to
use fewer concurrent connections, is very unlikely to help the situation as
you're adding lots more work and time spent to the operation.
> I'd like all the
> appended Events association to be inserted in one go. Is there a way
> to do this? Am I correct about what's going on?
SQLAlchemy 0.7 will also perform the INSERT of all the above records as a
single prepared statement if you assign a primary key value to the objects
ahead of time; the ORM no longer needs to fetch the newly inserted primary key
in this case, so you'd see a single statement sent to the DBAPI, and the DBAPI
then executes the statement in a more efficient manner for each set of
parameters (typically using prepared statement mechanics). It still
ultimately emits individual INSERT statements over the wire but just at a
lower, more performant level on the Python side.
Or, you can get the same effect by inserting the records using a non-ORM
executemany, where in this case you wouldn't need to pre-assign primary key
values:
Session.execute(Event.__table__.insert(), params=[{a:evt.a, b:evt.b, ...} for
evt in events}]
the disadvantage to the above is that the Session doesn't know about these
newly inserted Event objects. You'd need to make sure you don't add equivalent
records to the Session itself or they will conflict, and if you want to work
with these Event objects in the Session immediately subsequent to the above
operation you'd need to load them in (as that would be the only way to get at
their newly generated primary key values).
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
