Retrieving data from PostgreSQL to .NET application – performance test – surprising results

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I did some performance tests (retrieving data from DB to .NET
application using Npgsql driver)  and I found that for command type ==
StoredProcedure is better to first call function Prepare(). But,  for
command type == Text (direct query) performance is better if I do not
run function Prepare().  Why it is so? I thought that Prepare() should
boost performance of executed query. Why executing stored procedure
with Prepare() is faster but direct query is slower?

Also I wonder why executing direct query is the fasted method for
retrieving data. Shouldn't stored procedures be faster?

Some details:
-10 000 iterations
-single connection for all iterations
-PostgreSQL on Windows
-query (the same in stored procedure)

SELECT count(*)
FROM    "tableA" a
LEFT JOIN "tableB" b ON a.id = b."tableA_id"
LEFT JOIN "tableC" c ON b.id = c."tableB_id";


Results:
SP with Prepare() (Prepare for each iteration) 05.75s
SP with Prepare() (Prepare is called only for first iteration)
             12.81s
SP without Prepare()
                                               12.87s

Direct query with Prepare() (Prepare for each iteration):
            06.73s
Direct query with Prepare() (Prepare is called only for first
iteration)                 03.60s
Direct query without Prepare()
                                                     03.43s

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux