Home Man
Today's Posts

Linux & Unix Commands - Search Man Pages

RedHat 9 (Linux i386) - man page for prepare (redhat section 7)

PREPARE(7)				   SQL Commands 			       PREPARE(7)

       PREPARE - create a prepared query

	  PREPARE plan_name [ (datatype [, ...] ) ] AS query

	      An arbitrary name given to this particular prepared query. It must be unique within
	      a single session, and is used to execute or remove a previously prepared query.

	      The data-type of a parameter to the prepared query.  To refer to the parameters  in
	      the prepared query itself, use $1, $2, etc.

	      The query has been prepared successfully.

       PREPARE	creates  a  prepared  query. A prepared query is a server-side object that can be
       used to optimize performance. When the PREPARE statement is executed, the specified  query
       is parsed, rewritten, and planned. When a subsequent EXECUTE statement is issued, the pre-
       pared query need only be executed. Thus, the parsing, rewriting, and planning  stages  are
       only performed once, instead of every time the query is executed.

       Prepared  queries  can take parameters: values that are substituted into the query when it
       is executed. To specify the parameters to a prepared query, include a list  of  data-types
       with  the PREPARE statement. In the query itself, you can refer to the parameters by posi-
       tion using $1, $2, etc. When executing the query, specify  the  actual  values  for  these
       parameters in the EXECUTE statement -- refer to EXECUTE [execute(7)] for more information.

       Prepared queries are stored locally (in the current backend), and only exist for the dura-
       tion of the current database session. When the client exits, the prepared query is forgot-
       ten,  and  so it must be re-created before being used again. This also means that a single
       prepared query cannot be used by multiple simultaneous  database  clients;  however,  each
       client can create their own prepared query to use.

       Prepared  queries  have	the  largest performance advantage when a single backend is being
       used to execute a large number of similar queries. The performance difference will be par-
       ticularly  significant  if the queries are complex to plan or rewrite. For example, if the
       query involves a join of many tables or requires the application of several rules. If  the
       query  is  relatively  simple to plan and rewrite but relatively expensive to execute, the
       performance advantage of prepared queries will be less noticeable.

       In some situations, the query plan produced by PostgreSQL for  a  prepared  query  may  be
       inferior  to  the plan produced if the query were submitted and executed normally. This is
       because when the query is planned (and the optimizer attempts  to  determine  the  optimal
       query  plan),  the actual values of any parameters specified in the query are unavailable.
       PostgreSQL collects statistics on the distribution of data in the table, and can use  con-
       stant  values  in  a query to make guesses about the likely result of executing the query.
       Since this data is unavailable when planning prepared queries with parameters, the  chosen
       plan may be sub-optimal.

       For  more  information  on  query  planning and the statistics collected by PostgreSQL for
       query optimization purposes, see the ANALYZE [analyze(7)] documentation.

       SQL92 includes a PREPARE statement, but it is only for use in embedded  SQL  clients.  The
       PREPARE statement implemented by PostgreSQL also uses a somewhat different syntax.

SQL - Language Statements		    2002-11-22				       PREPARE(7)

All times are GMT -4. The time now is 02:51 PM.

Unix & Linux Forums Content Copyrightę1993-2018. All Rights Reserved.
Show Password