Thanks Mr. Michael,
I will make changes in my code to reflect your tips and I will analyse the
results. Thanks a lot.
Att,
Jan Palach
--------------
See my linkedin: http://www.linkedin.com/pub/jan-palach/16/b1a/7a3
On Feb 15, 2013, at 1:11 PM, Michael Bayer <[email protected]> wrote:
> 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 <[email protected]> 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 [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.
>>
>>
>
>
> --
> 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.
>
>
--
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.
