LINQ to PostgreSQL insert 10 times slower than an SQL file.

LINQ to PostgreSQL insert 10 times slower than an SQL file.

Postby migle » Tue 19 Jan 2010 14:15

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
migle
 
Posts: 27
Joined: Tue 19 Jan 2010 13:52

Postby AndreyR » Wed 20 Jan 2010 16:06

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.
Last edited by AndreyR on Thu 21 Jan 2010 08:06, edited 1 time in total.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby migle » Wed 20 Jan 2010 20:12

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
migle
 
Posts: 27
Joined: Tue 19 Jan 2010 13:52

Postby migle » Wed 20 Jan 2010 22:47

Actually, PgSqlLoader is not being slower than the SQL file. My mistake, mixed numbers.
migle
 
Posts: 27
Joined: Tue 19 Jan 2010 13:52

Postby AndreyR » Mon 05 Jul 2010 15:56

We have added some insert performance improvement in the upcoming build. We plan to release the build this week.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to LinqConnect (LINQ to SQL support)