Thursday, June 27, 2013

Performance improvements when creating NpgsqlConnection objects

Recently, I applied a patch from Kevin Pullin which will improve the performance of programs using Npgsql. This patch reduces significantly the time to create new NpgsqlConnection objects. This particularly applies in scenarios where you are creating and disposing a lot of NpgsqlConnection objects, like when you are using connection pool, ( you are using it, right? :) ).

Comparison test

I made an artificial test to show the impact of this patch. This test consists of a simple loop where I create 10k NpgsqlConnection objects. 

    class Program
    {
        static void Main(string[] args)
        {
 
            var connString = "server=127.0.0.1;userid=npgsql_tests;database=npgsql_tests;";
                        
            Stopwatch sw = Stopwatch.StartNew();
                        
            for (int i = 0i < 10000i++)
            {
                var conn = new NpgsqlConnection(connString);
                
            }
            
            sw.Stop();
                        
            Console.WriteLine(sw.ElapsedMilliseconds);
 
            
 
        }
    }


These are the results 

Code without patch:

  • 1st run: 1316 ms
  • 2nd run: 1333 ms
  • 3rd run: 1310 ms
  • Average time: 1319 ms

Code with patch:

  • 1st run: 33 ms
  • 2nd run: 39 ms
  • 3rd run: 33 ms
  • Average time: 35 ms

The new code, on this test, was more than 30 times faster! Of course this doesn't mean your code will be 30 times faster, after all your code doesn't consist of only creating NpgsqlConnection objects, but imagine a high traffic server which receives a lot of requests. When you sum up all the time spent creating NpgsqlConnection objects, this performance gain would make a difference.

Please, give it a try and let me know how it works for you. Just go to Npgsql git page and press the "Download ZIP" button and get a snapshot of the code. Open the Npgsql2010.sln solution file, build and test! 

Again, thank you very much, Kevin, for your patch!


No comments: