Devart Forums

Hi,

I'm trying to insert a lot of records onto a PostgreSQL table, which, for now, has no indices other than it's primary key (bigserial).
I need to achieve about 10 K records per second performance or near.

If I write an SQL file with 100 thousand inserts I get 12.5 to 15 K records per second.
However, when using LINQ to PostgreSQL code, I get only about 1000-1100 records per second, which is useless.

I have removed all associations from the lqml file and generated code.
Everything is wrapped in a transaction.
I used PgSqlMonitor to verify that only the insert statements are being executed.

PgSqlMonitor shows that only inserts are being executed, there's only one catch: every insert statement is being prepared and then executed.
That is instead of preparing the statement only once and executing it several times.


What might be wrong here?
Might it be the fact that the statement is being prepared every time?
Can I turn off prepared statements (which are useless if used this way)?

How can I get this faster?
Is LINQ to PostgreSQL inherently useless for more than 1000 records per second?

Miguel

We are working on performance improvement in case of a batch of inserts.
I will let you know as soon as this functionality is available.

I found out about PgSqlLoader in the meanwhile. It uses PostgreSQL COPY command and achieves just about the required performance, 1 can insert 11K records/second.

It is acceptable for me to use an alternative interface when inserting these more abundant entities...
Still, I find it curious that it is still lower than a text SQL file fed onto the command line client, even though it uses COPY.

I hope I can load these things with LINQ to PostgreSQL... I also need 10K+/second for this....

Miguel

Actually, PgSqlLoader is not being slower than the SQL file. My mistake, mixed numbers.

We have added some insert performance improvement in the upcoming build. We plan to release the build this week.