[#1010495] Bad performance calling a (empty) function very often

View Trackers | Bugs | Download .csv | Monitor

2008-10-22 14:19
Submitted by:
Andreas Nolte (andreasn)
Assigned to:
Nobody (None)
Npgsql Version:
Bad performance calling a (empty) function very often

Detailed description
I built a test case to check our current SQLServer application against PostgreSQL 8.3.3.
I migrated all stored procedures into postgresql functions and used npgsql.
One test case is to insert approx. 7000 values into the db, by calling a function.

execution times for this test case:
SQL Server: 3-4 sec.
PostgreSQL/Npgsql: 45 sec.
PostgreSQL/ODBC: 4-5 sec.

The interesting issue is, even if you delete the whole code inside the postgresql-function the execution time doesn't reduce much. Only from 45 sec to 42 sec. So it seems, that there is a huge administration overhead in the Npgsql layer (but this is only interpretation of course).
The PostgreSQL-ODBC driver seems to be much leaner in this case.


Date: 2009-01-14 16:04
Sender: Andreas Nolte

OK.. reusing the command object was the goal.
Now I got 30k of data in 10s instead of 170sec
It works. TX for your effort.
Date: 2009-01-14 15:13
Sender: Francisco Figueiredo jr.

Strange... It was supposed to be working on 2.0.2

What do you get? Is the performance still bad?

Note that in order to get the benefits of this improvement you need to reuse the NpgsqlCommand object.

I hope it helps.
Date: 2009-01-13 11:11
Sender: Andreas Nolte

I tested it with the new version 2.0.2, but unfortunately
without any success.
Date: 2008-11-11 18:02
Sender: Francisco Figueiredo jr.

Fixed in cvs.

On my tests I could call a function 10k in 2.7 secs.

Please, give it a try and let me know if you have any problems.
Date: 2008-10-23 23:26
Sender: Francisco Figueiredo jr.

HI, Andreas!

Your interpretation is right. There is too much overhead when calling functions. We have a lot of checks to do which takes a lot of time. I'm going to work on this issue to give better performance when working with function calls.

Thanks for heads up about this.

Attached Files:


Field Old Value Date By
close_date2009-01-14 16:042009-01-14 16:04andreasn
close_date2009-01-14 15:132009-01-14 15:13fxjr
close_date2009-01-13 11:112009-01-13 11:11andreasn
status_idOpen2008-11-11 18:02fxjr
ResolutionNone2008-11-11 18:02fxjr
close_date2008-11-11 18:022008-11-11 18:02fxjr
Powered By FusionForge