1

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.

Community
  • 1
  • 1
stackmike_2014
  • 37
  • 7
  • 1
    If you're doing 1000 individual SQL inserts, then you're going t have a ot of round-trips to the DB -- if it's on another server, that's going to be slow, especially over tcp/ip. Now, if you can use a bulk insert sql then it'll be quite a bit faster... are you writing the SQL or can you tell if it's a 1 insert per row or a bulk insert? – Joe Love Apr 21 '15 at 03:48
  • Hi @Joe, it's a bulk insert, many records inserted in 1 transaction, I use parameters which helps a fair bit... I'll give Mike's suggestion a try first thing tomorrow and post back here. – stackmike_2014 Apr 21 '15 at 03:54
  • I don't mean a bunch of inserts in 1 transaction. I mean a bunch of inserts in 1 statement. Example of inserting 2 rows with 1 statement: INSERT INTO films (code, title, did, date_prod, kind) VALUES ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'), ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'); – Joe Love Apr 21 '15 at 04:38
  • No, it's 1 insert and one SQL statement. I used OdbcParameter. – stackmike_2014 Apr 21 '15 at 13:16
  • Ok, so apparently set autocommit to off is no longer supported in current versions of postgres, as per an error message I get when executing @Mike's SQL statement, "SET AUTOCOMMIT TO OFF is no longer supported." – stackmike_2014 Apr 21 '15 at 15:51
  • 2
    just say "begin transaction" and "commit" when finshed. Note: the word "transaction" is optional. Again though if you use a multi-insert statement, you'll get much better performance. Alternatively, you can use the copy command – Joe Love Apr 21 '15 at 19:36
  • 1
    Thanks @Joe, I tried out multiple records per insert inside a transaction and achieved approximately x4 performance increase. Your advice made a big difference and removed a bottleneck, thanks a lot. – stackmike_2014 Apr 23 '15 at 16:01
  • I sumitted this as an answer. – Joe Love Apr 23 '15 at 20:57

1 Answers1

2

Doing multiple inserts per transactions isn't enough. Use a single, multi-row insert statement like so:

INSERT INTO films (code, title, did, date_prod, kind) 
VALUES 
('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'), 
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

The more records you can put into 1 insert, the faster it'll be. This is for multiple reasons and one reason is the round trips to the server-- there is at least 1 round trip per individual sql statement, when you're doing thousands of rows, this can be quite a drain on performance.

Joe Love
  • 4,498
  • 1
  • 17
  • 29