On Mar 26, 2013, at 6:41 PM, Laurence Rowe <[email protected]> wrote:
> > 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? It only does this if it doesn't have the primary key available already. If you add() a bunch of objects to a Session that already have the primary key populated, it will batch them together into a single executemany() call, provided there are no inter-row dependencies between the full set of rows. This behavior was introduced in 0.7. For the case where the PK is being generated by the database, in general, the DBAPI doesn't provide for a way to get "last inserted id" when you do an executemany(); either for DBAPIs that use cursor.lastrowid nor those where we have to call some kind of function like "select last_inserted"; it even doesn't work with INSERT..RETURNING, so even on those better platforms that provide RETURNING, we are still stuck with individual inserts when using the ORM, if we're relying upon auto-generated primary key values. > (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'd be cautious with UUID primary keys, as it implies that all the foreign keys that refer to that primary key are also UUIDs, which means you're using a lot more space for indexes overall. Ive seen Postgresql performance take a hit from using UUIDs as primary keys. > I had another idea to look for UOWTransaction.cycles evaluating True, but I > think that might be telling me something different. I dont think that would provide too much insight here. The main reason the ORM does individual inserts is to get at those PK values, so if you want to pre-populate those by calling a Postgresql sequence ahead of time, that could be a workable strategy. Though if you're really going for performance of inserts, I might recommend looking into using table.insert() directly in conjunction with a list of value sets, it will be much more performant than using the ORM. -- 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.
