Re: speed concerns with executemany()

On 01/01/2017 11:14 AM, mike bayer wrote:
>
>
> 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.

Same code across network, client in Bellingham WA, server in Fremont CA:

Without autocommit:

In [51]: %timeit -n 10 cur.executemany(sql, l)
10 loops, best of 3: 8.22 s per loop

With autocommit:

In [56]: %timeit -n 10 cur.executemany(sql, l)
10 loops, best of 3: 8.38 s per loop

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com