Skip site navigation (1) Skip section navigation (2)

Re: Extended query: prepared statements list?

From: Oliver Fromme <olli(at)lurza(dot)secnetix(dot)de>
To: marcbastin(at)mindspring(dot)com (M(dot) Bastin)
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Extended query: prepared statements list?
Date: 2004-07-15 20:45:28
Message-ID: 200407152045.i6FKjSrl026478@lurza.secnetix.de (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Marc,

M. Bastin wrote:
 > Yes and no.  I'm looking at the subject from the frontend-backend 
 > protocol 3.0 point of view which is separated in the Extended Query 
 > language and the Simple Query language.
 > 
 > With the PREPARE and EXECUTE statements you're accessing the Extended 
 > Query language through the Simple Query language, which requires more 
 > CPU time since your EXECUTE query itself is going to be parsed as a 
 > Simple Query first before PostgreSQL realizes it must execute a 
 > prepared statement, while when you send the commands directly through 
 > the Extended Query language you skip that parsing step.

Thanks for the explanation.  I've only been using Postgres
via the psql monitor and client applications (most of them
written in Python, some in Perl), so I'm not familiar with
the underlying client-server protocol.

You are right that the EXECUTE statement still has to be
parsed.  However, I think the pasrsing overhead is small,
because the EXECUTE command has a very simple structure
("EXECUTE <plan> <arguments>"), as opposed to, say, the
very complicated synopsis of a SELECT command.  Apart
from that, the overhead of the query planner is probably
much bigger, so using PREPARE + EXECUTE is probably still
a great win, I think.

I just wonder ...  I'm currently writing a client app (in
Python) which has to insert quite a lot of things.  This
is for a network traffic accounting system.  In particular,
the program reads accumulated accounting data from a file,
pre-processes it and creates appropriate INSERT statements
(up to several hundreds or even thousands per session).
I wonder if it will be worth to PREPARE those inserts and
the EXECUTE them.  But I guess it won't make much of a
difference, because the INSERT statements are very simple
anyway.

Best regards
   Oliver

-- 
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 M√ľnchen
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

With Perl you can manipulate text, interact with programs, talk over
networks, drive Web pages, perform arbitrary precision arithmetic,
and write programs that look like Snoopy swearing.

In response to

Responses

pgsql-novice by date

Next:From: M. BastinDate: 2004-07-15 21:07:56
Subject: Re: Extended query: prepared statements list?
Previous:From: Felix E. KleeDate: 2004-07-15 19:34:30
Subject: Re: Critique needed for contact-DB draft

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group