Performance Issue with bulk Insert of Rows to Temp Table

From: Vikram Rakhe <vikram(dot)rakhe(at)gmail(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Performance Issue with bulk Insert of Rows to Temp Table
Date: 2019-01-03 06:44:45
Message-ID: CALynjEvKjT_PGv5Qw_+nW2RV-CocK1u2801c-MUrc-8rMh99RQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

Hello,

I am trying to insert multiple rows to temp tables on postgreSQL database
using psqlodbc lib (version 10.4). I am using bind variables to insert the
rows to the table. When i gone through the logs i could see that it is
getting inserted one at a time using the same plan instead of bulk insert
of values.

CREATE TEMPORARY TABLE TEMP_uid_scratch2 ( auid VARCHAR(15) ) ;

2018-12-17 04:18:31 :LOG: execute _PLAN000000B173A9C420: INSERT INTO
TEMP_uid_scratch2 (auid) VALUES ($1 )
2018-12-17 04:18:31 :DETAIL: parameters: $1 = '6kYlmd1UI$ykTA'
2018-12-17 04:18:31 :LOG: duration: 0.030 ms
2018-12-17 04:18:31 :LOG: duration: 0.009 ms
2018-12-17 04:18:31 :LOG: execute _PLAN000000B173A9C420: INSERT INTO
TEMP_uid_scratch2 (auid) VALUES ($1 )
2018-12-17 04:18:31 :DETAIL: parameters: $1 = 'R4Zlmd1UI$ykTA'
2018-12-17 04:18:31 :LOG: duration: 0.031 ms
2018-12-17 04:18:31 :LOG: duration: 0.012 ms
2018-12-17 04:18:31 :LOG: execute _PLAN000000B173A9C420: INSERT INTO
TEMP_uid_scratch2 (auid) VALUES ($1 )
2018-12-17 04:18:31 :DETAIL: parameters: $1 = 'hAUlmd1UI$ykTA'
2018-12-17 04:18:31 :LOG: duration: 0.026 ms
2018-12-17 04:18:31 :LOG: duration: 0.011 ms
2018-12-17 04:18:31 :LOG: execute _PLAN000000B173A9C420: INSERT INTO
TEMP_uid_scratch2 (auid) VALUES ($1 )
2018-12-17 04:18:31 :DETAIL: parameters: $1 = 'hAQlmd1UI$ykTA'
2018-12-17 04:18:31 :LOG: duration: 0.033 ms
2018-12-17 04:18:31 :LOG: duration: 0.009 ms
2018-12-17 04:18:31 :LOG: execute _PLAN000000B173A9C420: INSERT INTO
TEMP_uid_scratch2 (auid) VALUES ($1 )
2018-12-17 04:18:31 :DETAIL: parameters: $1 = 'hAUlmd1UI$ykTA'
2018-12-17 04:18:31 :LOG: duration: 0.031 ms
2018-12-17 04:18:31 :LOG: duration: 0.009 ms

Results shows linear increase in time as well
80 rows - 16 ms
160 rows - 32 ms
32536 rows - 18 secs

I am using server side prepare by default. Is there any other way to
improve this or any configurations setting to make it efficient to insert
bulk rows instead of one row at a time?

Thanks,
Vikram Rakhe

Browse pgsql-odbc by date

  From Date Subject
Next Message William Hoffmann 2019-01-09 16:54:03 connecting to postgres database
Previous Message Igor Korot 2018-12-25 07:48:59 Re: NOTIFY/LISTEN with ODBC interface