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-multiple-statements 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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
