PgSqlLoader.Buffersize and Remote/Local performance

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Posts: 8
Joined: Wed 14 Apr 2010 09:26

PgSqlLoader.Buffersize and Remote/Local performance

Post by Tiago_G » Wed 14 Apr 2010 09:59


I am developing an application where i need to insert lots of records (several thousands) as fast as possible. To do this i am using PgSqlLoader.
By trying several values for PgSqlLoader.Buffersize i see that i get the best performance when it's about 256. Being the default 256K (and in the version before 32MB), i am quite puzzled. Shouldn't bigger values be faster? What exactly does this BufferSize do?

I also see ~2x performance gain if i execute my application on the server. The connection i have is Gigabit Ethernet, so i don't think 2x gain is to be expected. The bandwidth is not the bottleneck. What should i be looking into? Round trip-times? What other factors might create this difference between local/remote usage?



Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 16 Apr 2010 15:15

1. The best value for the BufferSize property can be defined experimentally in your environment. We will inverstigate the reason why greater value of BufferSize not always leads to increasing the PgSqlLoader performance.

2. I have compared the performance of PgSqlLoader locally (on my machine) and remotely (via local network). I've got 20% performance gain only. The round trip for local requests is faster than for remote ones. Probably, the reason of the big difference you recieved is not only ping time, but internal implementation of PostgreSQL server for network request processing or some network latency (please contact you system administrator).

Post Reply