Re: 10 row/second insert in ssis

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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>

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