npgsql/Npgsql — pull request #123 by Steven-Berry (2013-12-10 23:42:30+00:00)
Title: Improve performance of NpgsqlParameterCollection GetValue/IndexOf

By using a dictionary lookup instead of scanning the parameter list, the performance of commands with 10 or more parameters if much faster.

We have a case where we have SQL commands with lots of parameters. The time taken to lookup the parameter values in npgsql dwarfs the actual execution time of the command in postgres. I have improved the performance by creating a dictionary lookup that preserves the current behavior in regards to first matching names that are the same case and then matching parameter names of different casing.

I have included the timing of my machine of the current method vs using a dictionary.
Performance for 5 or more parameters is improved with the dictionary approach.
Each line is:
Number of runs X number of parameters in command = Total calls to GetValue in given amount of time

USING DICTIONARY
Case of parameterName string matches
100000 x 1 = 100000 in 20 ms
20000 x 5 = 100000 in 27 ms
10000 x 10 = 100000 in 26 ms
1000 x 100 = 100000 in 25 ms
100 x 1000 = 100000 in 22 ms
10 x 10000 = 100000 in 29 ms

Different casing of parameterName
100000 x 1 = 100000 in 34 ms
20000 x 5 = 100000 in 81 ms
10000 x 10 = 100000 in 106 ms
1000 x 100 = 100000 in 98 ms
100 x 1000 = 100000 in 104 ms
10 x 10000 = 100000 in 124 ms

CURRENT METHOD USING LOOP
Case of parameterName string matches
100000 x 1 = 100000 in 19 ms
20000 x 5 = 100000 in 45 ms
10000 x 10 = 100000 in 84 ms
1000 x 100 = 100000 in 769 ms
100 x 1000 = 100000 in 7605 ms
10 x 10000 = 100000 in 75229 ms

Different casing of parameterName
100000 x 1 = 100000 in 40 ms
20000 x 5 = 100000 in 99 ms
10000 x 10 = 100000 in 184 ms
1000 x 100 = 100000 in 1839 ms
100 x 1000 = 100000 in 17699 ms
10 x 10000 = 100000 in 179452 ms

Code for benchmark:

static void Main(string[] args)
{
PerformanceWithNParameters(100, true, false);

for (int d = 0; d < 2; d++)
{
Console.WriteLine(d == 1 ? "\r\n\r\nDifferent casing of parameterName" : "Case of parameterName string matches");

PerformanceWithNParameters(1, d == 1, true);
PerformanceWithNParameters(5, d == 1, true);
PerformanceWithNParameters(10, d == 1, true);
PerformanceWithNParameters(100, d == 1, true);
PerformanceWithNParameters(1000, d == 1, true);
PerformanceWithNParameters(10000, d == 1, true);
}
}

private static void PerformanceWithNParameters(int n, bool differentCase, bool report)
{
var command = new NpgsqlCommand();
var collection = command.Parameters;
for (int i = 0; i < n; i++)
{
collection.AddWithValue("value" + i, i);
}

var tn = 100000 / n;

var a = Stopwatch.StartNew();
int c = 0;
for (int t = 0; t < tn; t++)
{
for (int i = 0; i < n; i++)
{
NpgsqlParameter param;
if (!collection.TryGetValue((differentCase ? "VALUE" : "value") + i, out param))
{
throw new Exception();
}

c++;
}
}

a.Stop();
if (report)
{
Console.WriteLine("{0} x {1} = {2} in {3} ms", tn, n, c, a.ElapsedMilliseconds);
}
}