On Tuesday, 26 March 2013 14:54:08 UTC-7, Michael Bayer wrote:
>
>
> On Mar 26, 2013, at 5:24 PM, Laurence Rowe <lauren...@gmail.com<javascript:>>
> wrote:
>
> > I'd like to measure the number of database round trips that are
> associated with a request to my web application so I can write tests to
> catch potential performance regressions. I've been using
> mock.Mock(wraps=connection.execute) to keep count and while I think this
> works for selects, I don't have a good understanding of when executed
> statements result in serialised network round trips or when they can be
> 'pipelined', e.g. when inserting new rows.
> >
> > So to take a real example, posting to my web app results in the
> insertion of a row in each of four tables. Afterwards, the
> connection.execute mock's call_count is 4 and I see 4 INSERTs in my
> sqlalchemy log:
> >
> > INFO:sqlalchemy.engine.base.Engine:INSERT INTO resources (rid) VALUES
> (?)
> > INFO:sqlalchemy.engine.base.Engine:('7745b647ff154ff39cedb897d4e2983c',)
> > INFO:sqlalchemy.engine.base.Engine:INSERT INTO statements (rid,
> predicate, object, tid) VALUES (?, ?, ?, ?)
> > INFO:sqlalchemy.engine.base.Engine:('7745b647ff154ff39cedb897d4e2983c',
> 'organism', '{"scientific_name": "Homo sapiens", "taxon_id": 9606, "_uuid":
> "7745b647-ff15-4ff3-9ced-b897d4e2983c", "organism_name": "human"}',
> 'edea69795dd14eb6bed73321cff54471')
> > INFO:sqlalchemy.engine.base.Engine:INSERT INTO transactions (tid, data)
> VALUES (?, ?)
> > INFO:sqlalchemy.engine.base.Engine:('edea69795dd14eb6bed73321cff54471',
> '{"tid": "edea6979-5dd1-4eb6-bed7-3321cff54471", "description":
> "/organisms/", "user": " remoteuser:TEST"}')
> > INFO:sqlalchemy.engine.base.Engine:INSERT INTO current_statements (rid,
> predicate, sid) VALUES (?, ?, ?)
> > INFO:sqlalchemy.engine.base.Engine:('7745b647ff154ff39cedb897d4e2983c',
> 'organism', 1)
> >
> > Is there some way to tell which statements end up having data read from
> the cursor? Here I have one dependent insert which required the
> autoincremented primary key from a related row whereas the rest were fully
> specified.
>
> you can catch SQL traffic more directly using connection events, there are
> two varieties, one catches the high level SQL expression construct, and the
> other catches the activity at the level of cursor.execute() or
> cursor.executemany() (DBAPI cursor). Intercepting execute/executemany is
> the most accurate way to see all DBAPI interaction fully and exactly as
> it's being passed:
>
>
> http://docs.sqlalchemy.org/en/rel_0_8/core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute
>
>
> http://docs.sqlalchemy.org/en/rel_0_8/core/events.html#sqlalchemy.events.ConnectionEvents.after_cursor_execute
>
>
> next aspect, if you consider SELECT statements as those which read data
> from the cursor, you can tell if a cursor has results pending on it by
> checking if cursor.description is not None. You can check for this inside
> of the after_cursor_execute event.
>
> if you're trying to look at INSERT statements and distinguish between
> those which use an "implicit" autoincrementing primary key and those which
> have it specified, depending on specifics this might be more
> straightforward at the SQL expression level, if you are using
> table.insert() constructs (or via the ORM which also does). A generic
> way would be to look at the Table that's the subject of the insert, then
> looking at the parameters to see if parameters referring to a full primary
> key are present. These values are all present on the context passed to the
> execute events. Another way that might work more expediently, but is a
> little more of an "inside" method, is to take a look at
> context.inserted_primary_key inside of the after_cursor_execute event; if
> it contains a full primary key without the value None present, that means
> the dialect knew ahead of time the full primary key value (this will only
> be filled in for a single statement execute, not an executemany).
>
> I can work up an example using any combination of these techniques, if you
> can let me know which might seem workable.
Thanks, moving to a connection event seems like a cleaner way to measure
this than the mock.
When using the ORM, does SA always fetch any db generated primary key?
(Either through a sequence preexecute or by subsequently reading from the
cursor metadata depending on the dialect.) If not, then ideally I'd want
only those that were fetched for use in a subsequent insert. If so, then I
should probably tweak my structure - I'm only really using a non-UUID
primary key because it was the easiest way to get an autoincrementing
column in SQLite... Deployment will be on Postgres.
I had another idea to look for UOWTransaction.cycles evaluating True, but I
think that might be telling me something different.
Laurence
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
