From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Christophe Pettus <xof(at)thebuild(dot)com> |
Cc: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>, mike bayer <mike_mp(at)zzzcomputing(dot)com>, "psycopg(at)postgresql(dot)org" <psycopg(at)postgresql(dot)org> |
Subject: | Re: speed concerns with executemany() |
Date: | 2016-12-24 05:00:10 |
Message-ID: | 135fa407-af01-cef8-a809-8133115e6780@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
On 12/23/2016 06:57 PM, Christophe Pettus wrote:
>
>> On Dec 23, 2016, at 18:55, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>> Alright that I get. Still the practical outcome is each INSERT is being done in a transaction (an implicit one) so the transaction overhead comes into play. Or am I missing something?
>
> Nope, not missing a thing. The theory (and it is only that) is that when they do the .executemany(), each of those INSERTs pays the transaction overhead, while if they do one big INSERT, just that one statement does.
Just ran a quick and dirty test using IPython %timeit.
With a list of 200 tuples each which had 3 integers INSERTing into:
test=> \d psycopg_table
Table "public.psycopg_table"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |
The results where:
sql = "INSERT INTO psycopg_table VALUES(%s, %s, %s)"
Without autocommit:
In [65]: timeit -n 10 cur.executemany(sql, l)
10 loops, best of 3: 12.5 ms per loop
With autocommit:
In [72]: timeit -n 10 cur.executemany(sql, l)
10 loops, best of 3: 1.71 s per loop
>
> --
> -- Christophe Pettus
> xof(at)thebuild(dot)com
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Dorian Hoxha | 2016-12-25 09:11:09 | Re: speed concerns with executemany() |
Previous Message | Jim Nasby | 2016-12-24 04:20:57 | Re: speed concerns with executemany() |