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
LINQ to PostgreSQL insert 10 times slower than an SQL file.
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
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