I wrote a detailed answer to a previous question including examples for all allowed styles of INSERT on SO here: http://stackoverflow.com/questions/11769366/why-is-sqlalchemy-insert-with-sqlite-25-times-slower-than-using-sqlite3-directly/11769768#11769768. Though it seems you're familiar with most of this as I see you are already using bulk insert() (which note is *not* an ORM function, so already you're not really using the ORM).
In your specific examples you're comparing psycopg2 directly, which is written primarily in C, to a Core version - the psycopg2 version is also not using bound parameters which is a bad idea. Also the psycopg2 test appears to be inserting the entire batch in one giant statement where the core version is batching by 200. Another aspect of the Core test is that psycopg2 is also doing a unicode conversion on all parameters being sent, as we enable psycopg2.extensions.UNICODE. If you pass use_native_unicode=False to create_engine() this part will be disabled. So using similar batching would help, the unicode conversion will help, but after that, raw C code is vastly more performant than code that has some Python overhead to it. On Feb 15, 2013, at 8:17 AM, Jan Palach <pal...@gmail.com> wrote: > Hi everyone, first of all sorry my english :), this is my first email in this > mainlist. > > My name is Jan Palach I'm from Brazil and i'm novice in SQLALchemy > ecosystem. I'm testing the combination PostgreSQL + SQLAlchemy(using > psycopg2) and CPython 2.x, to create a database layer for backend > applications that process a large amount of data(internal's policies > questions impossibility the NoSQL uses, and we need the flexibility of ORM to > choice different databases for different environments). Well, I have the > following picture(for test only): > > - A windows 8 machine with PostgreSQL 9.2.3: There is a table mob.t_test with > the following structure: > - CUSTOMER_ID character 32 > - ID SERIAL > - NAME character 64 > - There is an index for name, id and customer_id. > - The database is listen for external connections through the local > network. > > - A OSX Mountain Lion Macbook Pro running scripts for test inserts in the > remote database above. > > I ran some experiments with two difference scripts, because I need to > choose which technology w'll use for the future. > > For insert 100k lines i got the following results: > > SCRIPT01[1]: Average ~17 seconds per iteration. > > SCRIPT02[2]: Average ~10 minutes per iteration. > > > For insert 500 lines i got the following results: > > SCRIPT01[1]: Average ~0.1 seconds per iteration. > > SCRIPT02[2]: Average ~7 seconds per iteration. > > > > > > I now that ORM add some overhead, but I believe that I'm making something > wrong. Do you help me to identify something to improve it, without losing > different databases flexibility? > > > > 1 - http://pastebin.com/62VppSH4 > 2 - http://pastebin.com/MHdc7AGx > > > Thanks. > > > > > Att, > > Jan Palach > > -------------- > See my linkedin: http://www.linkedin.com/pub/jan-palach/16/b1a/7a3 > > > > > -- > 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. > > -- 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.
