Re: speed concerns with executemany()

On 12/24/2016 12:00 AM, Adrian Klaver wrote:
> 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

please ensure you run this test with statements passing over a real
network connection and not localhost. makes a significant difference.

>
>
>>
>> --
>> -- Christophe Pettus
>> xof(at)thebuild(dot)com
>>
>
>