Re: Query preparation

John Lister wrote:
> I appreciate that the send operations (and responses) may be grouped
> into a single packet, but i was thinking of the scenario (which i would
> have thought is fairly common) where you prepare the statement and
> execute it multiple times with different parameters...
>
> This will result in the parse and describe(portal) commands being sent
> twice as is (unless i set the prepare threshold to be 1). Admittedly,
> this will probably not result in any extra packets as the total length
> of all commands is likely to be less than the packet length (the query
> size is likely to be the limiting factor here), but this causes extra
> load on both the server and client - although maybe not enough to worry
> about.
>
> I was thinking that you could parse/describe the query just once.

Incidentally, a customer of ours bumped into the overhead of the
describe portal step just yesterday. It doesn't cause any extra network
roundtrips, and is usually pretty cheap, but in this case they were
selecting a single row from a table with 145 columns thousands of times
in a loop. Furthermore, I believe most of the columns were NULL. They're
using a PreparedStatement, but we still send the Describe portal message
on every execution. A quick & dirty patch to cache the RowDescription
information cut over 10% off the total runtime of the application.

Does anyone see a problem with caching the result set descriptor
(RowDescription)? AFAICS it should never change after a statement is
prepared. If not, I'll polish up and submit the patch.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com