Re: Speeds using a transaction vrs not

Joel Fradkin wrote:
> 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