When stored procedures were introduced in the MySQL database engine I pretty soon ran some tests on the performance impact it had. In short - perfomance doubled just by taking a fairly simple query and putting it inside a stored procedure. Of course it could be much less since in the tests I never reused the query command - so it always recompiled the query plan for the regular (nonSP) commands. But still - the reasons for using SP:s are more than just performance (i.e read hide implementation detail from sourcecode = implementing an extra layer) so I never ran any more tests.
Now I wanted to run the same performance tests against Postgresql.
Consider this function (CASE #1):
CREATE OR REPLACE FUNCTION ipgetcountry2(in_ip bigint, in_ipverno integer)
RETURNS SETOF a_iprecord AS
'SELECT * FROM a_iprecord WHERE IPFROM<=$1 AND IPTO>=$1 and ipverno=$2'
LANGUAGE 'sql' VOLATILE;
Compared to writing C# code like (CASE #2):
string strSQLorSP = "SELECT * FROM a_iprecord WHERE IPFROM<=" + ip.ToString() + " AND IPTO>=" + ip.ToString() + " and ipverno=" + lIPVerNo.ToString();
Compared to writing C# code like (CASE #3):
string strSQLorSP = "SELECT * FROM a_iprecord WHERE IPFROM<=:ip1" + " AND IPTO>=:ip2 and ipverno=:in_ipvernmo";
oCommand.Parameters.Add(new Npgsql.NpgsqlParameter("ip1", DbType.Int64)).Value = ip;
oCommand.Parameters.Add(new Npgsql.NpgsqlParameter("ip2", DbType.Int64)).Value = ip;
oCommand.Parameters.Add(new Npgsql.NpgsqlParameter("in_ipverno", DbType.Int32)).Value = lIPVerNo;
(in this last case we are using parameters instead of direct sql)
And the results (the slowest case is set a point of 100, so points 200 would be twise as fast):
Case #1:
The fastest with 162,85 points.
Case #2:
The slowest with 100,00 points.
Case #3:
The middle main with 103,38 points. But pretty much like case 2.
In other words - yes use stored procedures as much as you can! And more - use parameters, you should always use it because of SQL Injection risks, but here you have a (pretty small but still) performance reason for as well.
