On Mar 26, 2013, at 5:24 PM, Laurence Rowe <[email protected]> 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.
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
