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@uni-heidelberg.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.