Performance Issue with bulk Insert of Rows to Temp Table
Vikram Rakhe <[email protected]> Thu, 3 Jan 2019 12:14:45 +0530
| Newsgroups | gmane.comp.db.postgresql.odbc |
|---|---|
| Message-ID | <CALynjEvKjT_PGv5Qw_+nW2RV-CocK1u2801c-MUrc-8rMh99RQ@mail.gmail.com> |
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