Re: difference on execution time between prepared statement in pgAdmin and through JDBC Stack

On Fri, 25 Feb 2011 10:51:56 +0100, Lars Feistner wrote:
> Hello Maciek,
>
> okay, thanx for the clarifcation.
> Here are the server side logs for the pgAdmin case:
> 2011-02-25 10:42:08 CET LOG: duration: 98.226 ms statement: execute
> t2 (1,7429);
> 2011-02-25 10:42:08 CET DETAIL: prepare: prepare t2 (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=$2))
> ORDER BY vec.examinationcontentversion_sequenceno ASC NULLS
> LAST
>
> Is there a chance to get the execution plan of the statement, when
> the statement is executed through JDBC stack?
>
> I have the same feeling about the edge case here, that is exactly why
> i am writing to this list;-)
> So still the question is how can i find out what is going on?
>
> Yesterday i also looked at the debug level logs of the jdbc driver
> but i found nothinng unusal.
>
> Any more hints?
>
> Thanx in advance
> Lars
>
> On 02/24/2011 07:38 PM, Maciek Sakrejda wrote:
>> A named prepared statement (which you seem to be using through
>> pgAdmin) is *not* the same thing as a PreparedStatement in JDBC.
>> Most
>> pertinently, JDBC is using an unnamed portal to execute the query,
>> which delays planning until the parameters are provided. I don't
>> think
>> this ever happens with a named prepared statement (it would help if
>> you provided server-side logs for the pgAdmin execution as well).
>> The
>> deferred planning behavior typically works to your advantage, but
>> you
>> may have run into some edge case here.
>>
>> Alternately, because the unnamed portal execution actually also does
>> the planning, I presume it's counted in the time of the statement
>> execution, whereas in the pgAdmin case, it looks like you're only
>> timing the execute, which is going against an already-planned (at
>> prepare time) query, so you may be comparing apples to oranges.
>>
>> ---
>> Maciek Sakrejda | System Architect | Truviso
>>
>> 1065 E. Hillsdale Blvd., Suite 215
>> Foster City, CA 94404
>> (650) 242-3500 Main
>> www.truviso.com
>>
>
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 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
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

At 90% it is backend problem, at this time I may only suggest you to
try tunnelling (TUN/TAP or SSH) solutions over WAN or UMTS, which adds
some nagle's algorithm (tcp_nodelay = false). JDBC driver design was
much more better here.

Regards,
Radek.