From: | "Peter G(dot)" <highlander3(dot)14(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Retrieving data from PostgreSQL to .NET application – performance test – surprising results |
Date: | 2009-02-14 16:11:43 |
Message-ID: | f972e8a90902140811q813d82cof052b2a05870128e@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Octavio Alvarez | 2009-02-14 18:13:07 | Slow queries from information schema |
Previous Message | Rajesh Kumar Mallah | 2009-02-14 11:58:32 | Re: scheduling autovacuum at lean hours only. |