lundi 20 avril 2015

Speed up Postgresql insert performance from vb.net

I have a Vb.Net app (VS2010) as well as Postgres 9.3 and psqlodbc version 9.3.4. I interact with my Postgres database via ODBCConnection and the PostgreSQL odbc driver.

Doing simple transactional bulk inserts into a database table (no indexes or other high tech stuff, just plain old records and one serial column as the primary key) is slow even when conducted on my local machine (that is, app writes data to local postgres database with no network involved). I have seen speeds of 1000-1500 rows per seconds. My hardware is Win7 64 bit, a Sata 6gbs hard drive, 8GB of ram.

As per the post by Maxim Egorushkin on How to speed up insertion performance in PostgreSQL, he suggested using:

SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast(SQL_AUTOCOMMIT_OFF), 0);

to prevent autocommit from occurring. I am wondering how you would call this subroutine from .net since it appears to be part of the WIN API and thus the "conn" variable is likely a handle to a non .Net object.

I am confident this approach or some other one should be able to speed things up: apparently the non-free PGNP ODBC driver for postgres can do 10s of 1000s of inserts per second.

Input on how to disable autocommit is much appreciated.

Aucun commentaire:

Enregistrer un commentaire