From: | Lars Feistner <feistner(at)justis(dot)de> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | difference on execution time between prepared statement in pgAdmin and through JDBC Stack |
Date: | 2011-02-24 15:16:59 |
Message-ID: | 4D66766B.30104@justis.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Hello everyone,
i am puzzled like many other users of this list before. I read a lot of
old threads today, but nothing seemed to explain what i see in my log files.
I am using Postgres 9 on linux and the appropriate JDBC driver for this
database. I can also see the same behaviour on production system running
postgres 8.4.
My query:
prepare t1 (int4, int4) AS
SELECT DISTINCT vec.*, s.statistic_difficulty as examination_difficulty,
s.statistic_discriminatorypower as examination_discriminatorypower,
vimc.mediacount,vis.difficulty, vis.discriminatorypower,
vis.statistic_counter, virc.reviewcount, u.user_surname
FROM ims_vexaminationcontent vec
LEFT OUTER JOIN ims_statistic s ON vec.item_id = s.object_id AND
s.examination_id = vec.examination_id
LEFT OUTER JOIN ims_vitemmediacount vimc ON (vimc.item_id = vec.item_id)
LEFT OUTER JOIN ims_vitemstatistic vis ON (vis.item_id = vec.item_id)
INNER JOIN ims_user u on (author_id = u.user_id)
LEFT OUTER JOIN ims_vitemreviewcount virc ON virc.item_id = vec.item_id
WHERE ((vec.examinationcontentversion_version=1 AND
vec.examination_entityid=7429))
ORDER BY vec.examinationcontentversion_sequenceno ASC NULLS LAST
and following execute t1 (1,7429); runs in approx. 100 ms.
If a run the same query through my web application (using the same
parameters) my server log file tells me the statement takes approx.
1100ms. This is factor 10!
duration: 1151.088 ms execute <unnamed>: SELECT DISTINCT vec.*,
s.statistic_difficulty as examination_difficulty,
s.statistic_discriminatorypower as examination_discriminatorypower,
vimc.mediacount,vis.difficulty, vis.discriminatorypower,
vis.statistic_counter, virc.reviewcount, u.user_surname FROM
ims_vexaminationcontent vec LEFT OUTER JOIN ims_statistic s ON
vec.item_id = s.object_id AND s.examination_id = vec.examination_id LEFT
OUTER JOIN ims_vitemmediacount vimc ON (vimc.item_id = vec.item_id) LEFT
OUTER JOIN ims_vitemstatistic vis ON (vis.item_id = vec.item_id) INNER
JOIN ims_user u on (author_id = u.user_id) LEFT OUTER JOIN
ims_vitemreviewcount virc ON virc.item_id = vec.item_id WHERE
((vec.examinationcontentversion_version=$1 AND
vec.examination_entityid=$2)) ORDER BY
vec.examinationcontentversion_sequenceno ASC NULLS LAST LIMIT
2011-02-24 15:22:16 CET DETAIL: parameters: $1 = '1', $2 = '7429'
As you can see the application uses unnamed prepared statements which i
learnt today executes query planning everytime the statement runs and
after the variables are bound.
So i don't understand the difference between running the query through
the jdbc stack.
Thank you for any helpful link or explanation in advance.
Lars
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Lars Feistner
Kompetenzzentrum für Prüfungen in der Medizin
Medizinische Fakultät Heidelberg,
Im Neuenheimer Feld 346, Raum 013
69120 Heidelberg
E-Mail: feistner(at)uni-heidelberg(dot)de
Fon: +49-6221-56-8269
Fax: +49-6221-56-7175
WWW: http://www.ims-m.de
http://www.kompmed.de
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From | Date | Subject | |
---|---|---|---|
Next Message | Radosław Smogura | 2011-02-24 17:07:51 | Re: Slow query execution over WAN network |
Previous Message | Clemens Eisserer | 2011-02-24 14:56:10 | Slow query execution over WAN network |