Ah yes I'm not sure of the terminology either, i got the 'extended' from the mysqldump usage info (mysqldump -e, on by default but you can turn it off with mysqldump --extended-insert=false)
Anyway, from looking at the mysqldump output, extended inserts are covered by this bit from the INSERT documentation: "INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example: INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);" So its the difference between a single INSERT with lots of data vs a separate INSERT for each row The execute many thing is an option, but the relationships are a little complex (26 tables and counting). There is only one object added to the session, everything else is linked from there. Implementing the inserts without the ORM might get a little involved when it comes to ensuring the relations are established correctly, but if that's the way to do it then so be it. I am also open to the possibility to rejiggering my schema/session usage to fit better with SA, which is a fairly undefined goal I know. One thing that is very common in my program is a lot of (datetime, float) observations. They are all mapped to different objects and all go in different tables as they belong to different objects by the business logic. I'm not sure if it would be better to put them in one table and use a secondary relationship table with 2 foreign keys mapping objects to their given date/float observations. Indeed I had it that way originally but it has not seemed to have any impact on performance either way. Thanks for your reply On Nov 2, 5:38 am, Michael Bayer <mike...@zzzcomputing.com> wrote: > On Nov 1, 2009, at 9:52 AM, BEES INC wrote: > > > > > > > Hi > > > Does sqlalchemy support mysql extended insert syntax? > > I have been chasing down very slow performance. My program generates a > > whole bunch of data then does a big SA commit() at the end, which is > > proving to be inconveniently slow. > > > This is a raw import of a mysqldump without extended inserts > > > dbregress$ time mysql -ubees -pbees tt_test < up50ma.dbdump6 > > > real 0m33.043s > > user 0m1.343s > > sys 0m1.572s > > dbregress$ > > > Which roughly corresponds to the time it is taking SA to do the > > commit. > > > This is a raw import using extended inserts: > > > dbregress$ time mysql -ubees -pbees tt_test < up50ma.dbdump6.e > > > real 0m5.517s > > user 0m0.081s > > sys 0m0.024s > > dbregress$ > > > Which is clearly noticeably faster. > > > Also this is for a test dataset, the real dataset is much larger, and > > when used the final commit can take upwards of 25 minutes. > > > I could not see anything in the documentation regarding this, > > apologies if i missed something. I tried periodic commits vs a single > > commit at the end but this did not offer any real difference in > > performance, and also introduced other issues. > > you'll get much faster performance for bulk inserts if you forego the > usage of the ORM and use executemany() syntax which is documented here: > > http://www.sqlalchemy.org/docs/05/sqlexpression.html#executing-multip... > > I've googled and searched MySQL's site for "extended insert syntax" > and while there are many mentions of it, there are no definitions or > examples I can find. For example the word "extended" does not appear > on this page in any form: http://dev.mysql.com/doc/refman/5.0/en/insert.html > . > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
