Sunday, April 04, 2010

Function call performance optimizations

On my last post about that subject, I wrote about some optimizations I did to get better performance when calling functions with Npgsql.

While that optimizations were very nice, they had a drawback: you had to reuse your NpgsqlCommand object. You had to reuse it because the optimizations were based on cached data and if you created a new NpgsqlCommand object the data would need to be cached again.

In the general case, where you would create many NpgsqlCommand objects and call functions with them, you would not benefit from those optimizations.

In order to fix that, Noah Misch created a patch which remove 2 of the 3 internal calls which were giving performance problems.

The only case left is for functions which have return type of 'record'. We are working to get this case also covered.

I'm going to show here how much performance improvement you get with this patch with a simple call to a function which returns an integer. This function is on Npgsql unit test suite, but I reproduce it here just for completeness:

create function funcA() returns int as '
select 0;

' language 'sql';

I'm going to compare the latest stable release version Npgsql 2.0.8 with our latest cvs version with Noah's patch.

In a loop of 100 iteractions, this is what we get with Npgsql 2.0.8 and Npgsql cvs:

Npgsql 2.0.8:

time mono teste.exe

real 0m0.537s
user 0m0.457s
sys 0m0.028s

Npgsql cvs:

time mono teste.exe

real 0m0.467s
user 0m0.420s
sys 0m0.026s

It is 13% faster!

If we raise the number of interactions to 1000 we get:

Npgsql 2.0.8:

time mono teste.exe

real 0m1.237s
user 0m0.698s
sys 0m0.089s

Npgsql cvs:

time mono teste.exe

real 0m0.655s
user 0m0.492s
sys 0m0.054s

Which gives 47% improvement!

So, when next Npgsql release is out, we can see a modest to good performance improvement in function calling scenarios using Npgsql.

If you want to try it out today, please grab the latest cvs code and let us know what do you get.

Please, leave your comments and feedback. Also, participate on our Forums so you can share your experience.

No comments: