Re: 10 row/second insert in ssis - Mailing list pgsql-odbc

On 03/30/2016 03:35 PM, Bill Kuhn wrote:

Ccing list
> Adrian,
>
> Sorry for not including details earlier.
>
> Here is detail information:
> psqlodbc version = psqlodbc_09_05_0100-x86
> sql server version = 11.0.3401.0 (2012)
> postgres version = PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by
> gcc (Debian 4.9.2-10) 4.9.2, 64-bit
> client/ETL tool = ssis
> postgresql log = ?  I don't have ssh access to the box.  If this
> information is needed I'll work with the admin to get it
> odbc log = didn't see anything jumping out at me but I attached a copy
> if you'd like to take a look
>
> Iteration 1:
> 1.  Created a odbc system dsn via odbad32 tool (windows) and
> successfully tested connection
> 2.  Created an odbc connection manager (ssis) using odbc dsn created in
> step #1
> 3.  Created an odbc destination using connection manager created in step #2
> 4.  Created a source (sql server ole db, query that select a single
> integer column)
> 5.  Mapped the source column to the destination column
> 6.  Executed data flow for 100 records
> 7.  Observed execution time = 10 seconds (10 records/second)
>
> Iteration 2:
> The only thing I changed from Iteration 1 was to use an ado.net
> <http://ado.net> connection manager but it still used the same odbc dsn.
> Same result.
>
> To prove that postgres is not having issues I cross joined the table
> several times in postgres and inserted 11 million records in 14
> seconds.  I did this just to make sure it wasn't something poorly
> configured on the postgres side.

>
> Finally, I reproduced the steps in Iteration 1 on my laptop (originally
> I was developing on a remote server) and I observed the same 10
> records/second throughput.
>
> After trying the above I then resorted to tweaking a setting (either on
> the odbc dsn or within ssis), running, not seeing any difference,
> reverting, tweaking a different setting, etc.  No change in performance
> regardless of any settings.
>
> I work a lot with sql server and oracle in ssis and I'm used to commits
> being done in batches.  With postgresql it seems like each insert is
> being executed/committed separately (not in batch).


By default psqlodbc runs in autocommit mode which would explain the above.

I do not use SSIS so I am not going to be of much help there. All I can
say is maybe check the 2,3,4 steps you mention above for a setting that
turns autocommit off. This means though there will need to be  explicit
BEGIN/COMMIT commands somewhere. Do you know how the Oracle setup does that?

You might also want to look at:

https://technet.microsoft.com/en-us/library/ms131281%28v=sql.105%29.aspx

>
> Thanks in advance for any help you can provide.
>
> My real task is to migrate an entire database containing a few hundred
> million rows from sql server to postgresql and continue to send data to
> it every 10 minutes via an ssis package.  I have no doubt that I could
> figure out a way (dump to text and load into postgres) to load the
> database initially but given the throughput I'm seeing the incremental
> load would likely not be able to keep up.
>
>
>
> On Wed, Mar 30, 2016 at 4:31 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 03/30/2016 12:00 PM, Bill Kuhn wrote:
>
>         Greetings.
>
>         Is there anything special to configure to use the 32 bit odbc
>         driver?
>
>         I'm attempting to use the odbc driver to transfer data from
>         microsoft
>         sql server to postgresql and I'm seeing dismal insert
>         performance.  It
>         is as if each insert is being performed individually.
>
>         The rows I'm inserting only have one integer column.
>
>         Is there anything special that needs to be done to configure the
>         driver
>         to improve performance?
>
>
>     Well given the almost complete lack of information provided, I would
>     the answer is maybe, depending on:
>
>     psqlodbc version?
>
>     SQL Server version?
>
>     Postgres version?
>
>     Exactly how are you transferring the data?
>
>     Or to put it another way, what client are you using?
>
>     What does the Postgres log show?
>
>     What does the ODBC log show?
>
>
>         Many thanks.
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com