No I did not do it in on transaction (although in .net I never started or commited a transaction.
All inserts/updates/etc take place within a transaction with PostgreSQL. Some client libraries autocommit for you - you'll need to read the documentation.
ODBC : myCommand.CommandText = insertsqltext myCommand.ExecuteNonQuery() myTrans.Commit()
.net driver: Dim cmd As New NpgsqlCommand(insertsqltext, cnn) cmd.ExecuteNonQuery() cmd.Dispose()
Both look to me like they are producing one transaction per insert (the slowest possible way to bulk-copy data). That's assuming each block of commands is within one loop.
Precisely what is happening will be easiest to see by turning statement logging on in your postgresql.conf and comparing two runs. The delay might be in overheads of setting up the transaction etc. with the ODBC driver.
In any case, if bulk-copying data you'll find a huge improvement grouping rows together in batches of 100 - 10,000.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
