Thread

1. Prepared statements versus stored procedures

   Simon Connah <simon.n.connah@protonmail.com> — 2023-11-19T17:30:20Z

   Hi,
   
   First of all please forgive me. I'm not very experienced with databases.
   
   I was reading about prepared statements and how they allow the server to plan the query in advance so that if you execute that query multiple times it gets sped up as the database has already done the planning work.
   
   My question is this. If I make a stored procedure doesn't the database already pre-plan and optimise the query because it has access to the whole query? Or could I create a stored procedure and then turn it into a prepared statement for more speed? I was also thinking a stored procedure would help as it requires less network round trips as the query is already on the server.
   
   Sorry for the question but I'm not entirely sure how stored procedures and prepared statements work together.

2. Re: Prepared statements versus stored procedures

   David G. Johnston <david.g.johnston@gmail.com> — 2023-11-19T18:07:38Z

   On Sun, Nov 19, 2023 at 10:30 AM Simon Connah <simon.n.connah@protonmail.com>
   wrote:
   
   > My question is this. If I make a stored procedure doesn't the database
   > already pre-plan and optimise the query because it has access to the whole
   > query?
   
   
   No.  Planning isn't about the text of the query, it's about the current
   state of the database.
   
   Or could I create a stored procedure and then turn it into a prepared
   > statement for more speed?
   
   
   Not usually.
   
   I was also thinking a stored procedure would help as it requires less
   > network round trips as the query is already on the server.
   >
   
   Unless your query is insanely large this benefit seems marginal.
   
   
   > Sorry for the question but I'm not entirely sure how stored procedures and
   > prepared statements work together.
   
   
   They don't.
   
   David J.
   

3. Re: Prepared statements versus stored procedures

   Francisco Olarte <folarte@peoplecall.com> — 2023-11-19T18:09:07Z

   Hi Simon:
   
   On Sun, 19 Nov 2023 at 18:30, Simon Connah
   <simon.n.connah@protonmail.com> wrote:
   
   > I was reading about prepared statements and how they allow the server to plan the query in advance so that if you execute that query multiple times it gets sped up as the database has already done the planning work.
   
   But bear in mind that, if you use parameters, it does not have access
   to the whole query, so it has to make a generic plan. Many times it
   does not matter, but sometimes it does ( i.e. testing columns with
   very skewed value distributions, if you have an X column, indexed,
   where 99% of the values are 1 querying for X=1 is faster using a
   sequential scan when X=1 and an index scan when not, if you send X in
   a parameter the server does not know its real value ).
   
   > My question is this. If I make a stored procedure doesn't the database already pre-plan and optimise the query because it has access to the whole query?
   
   IIRC it does not, because it may not have access to all values, and
   more importantly, it does not have access to current statistics. Think
   of the typical case, preparing a database for an application, with
   empty tables and several procedures. On the first run, sequential
   scans ( to recheck for emptiness ) will be faster for every query.
   After some time of entering data ( and updating statistics ) better
   plans will surface. If you compiled the procedures on definition you
   would be stuck with seq scans forever. IIRC it does it once per
   transaction, but it should be in the docs.
   
   > Or could I create a stored procedure and then turn it into a prepared statement for more speed?
   > I was also thinking a stored procedure would help as it requires less network round trips as the query is already on the server.
   
   The main speed improvement of stored procedures is normally the less
   roundtrips ( and marshalling of queries back and forth ). You do not
   turn a stored procedure into a statement, you turn CALLING the stored
   procedure into a prepared statement, which may save some time but not
   that much, planning a call is easy.
   
   Other thing would be turning a stored procedure call into a prepared
   statement for an inline procedure, but this is something else.
   
   Francisco Olarte.
   
   
   
   

4. Re: Prepared statements versus stored procedures

   Simon Connah <simon.n.connah@protonmail.com> — 2023-11-19T18:36:06Z

   On Sunday, 19 November 2023 at 18:09, Francisco Olarte <folarte@peoplecall.com> wrote:
   > 
   
   > 
   
   > Hi Simon:
   > 
   
   > On Sun, 19 Nov 2023 at 18:30, Simon Connah
   > simon.n.connah@protonmail.com wrote:
   > 
   
   > > I was reading about prepared statements and how they allow the server to plan the query in advance so that if you execute that query multiple times it gets sped up as the database has already done the planning work.
   > 
   
   > 
   
   > But bear in mind that, if you use parameters, it does not have access
   > to the whole query, so it has to make a generic plan. Many times it
   > does not matter, but sometimes it does ( i.e. testing columns with
   > very skewed value distributions, if you have an X column, indexed,
   > where 99% of the values are 1 querying for X=1 is faster using a
   > sequential scan when X=1 and an index scan when not, if you send X in
   > a parameter the server does not know its real value ).
   > 
   
   > > My question is this. If I make a stored procedure doesn't the database already pre-plan and optimise the query because it has access to the whole query?
   > 
   
   > 
   
   > IIRC it does not, because it may not have access to all values, and
   > more importantly, it does not have access to current statistics. Think
   > of the typical case, preparing a database for an application, with
   > empty tables and several procedures. On the first run, sequential
   > scans ( to recheck for emptiness ) will be faster for every query.
   > After some time of entering data ( and updating statistics ) better
   > plans will surface. If you compiled the procedures on definition you
   > would be stuck with seq scans forever. IIRC it does it once per
   > transaction, but it should be in the docs.
   > 
   
   > > Or could I create a stored procedure and then turn it into a prepared statement for more speed?
   > > I was also thinking a stored procedure would help as it requires less network round trips as the query is already on the server.
   > 
   
   > 
   
   > The main speed improvement of stored procedures is normally the less
   > roundtrips ( and marshalling of queries back and forth ). You do not
   > turn a stored procedure into a statement, you turn CALLING the stored
   > procedure into a prepared statement, which may save some time but not
   > that much, planning a call is easy.
   > 
   
   > Other thing would be turning a stored procedure call into a prepared
   > statement for an inline procedure, but this is something else.
   > 
   
   > Francisco Olarte.
   
   Thank you very much for the explanation. I really appreciate it.
   
   Simon.

5. Re: Prepared statements versus stored procedures

   David G. Johnston <david.g.johnston@gmail.com> — 2023-11-19T19:40:12Z

   On Sun, Nov 19, 2023 at 11:09 AM Francisco Olarte <folarte@peoplecall.com>
   wrote:
   
   > IIRC it does it once per
   > transaction, but it should be in the docs.
   >
   
   There is no external caching for executing a CALL; the runtime executes the
   procedure afresh each time.  If it were any different that would have to be
   documented.
   
   You do not
   > turn a stored procedure into a statement, you turn CALLING the stored
   > procedure into a prepared statement,
   
   
   Which is not possible.  CALL is not a valid target for PREPARE; the valid
   ones are documented.
   
   The fact that store procedures do not return result sets - and are
   procedures - and prepared statements are not procedures and can return
   result sets makes any kind of direct comparison pretty meaningless in
   practice.  They do different things and solve different problems.  Know
   what the problem you are trying to solve is and which of the two are
   plausible options will make itself clear.
   
   David J.
   

6. Re: Prepared statements versus stored procedures

   Laurenz Albe <laurenz.albe@cybertec.at> — 2023-11-20T10:07:23Z

   On Sun, 2023-11-19 at 17:30 +0000, Simon Connah wrote:
   > I was reading about prepared statements and how they allow the server to
   > plan the query in advance so that if you execute that query multiple times
   > it gets sped up as the database has already done the planning work.
   > 
   > My question is this. If I make a stored procedure doesn't the database
   > already pre-plan and optimise the query because it has access to the whole
   > query? Or could I create a stored procedure and then turn it into a prepared
   > statement for more speed? I was also thinking a stored procedure would help
   > as it requires less network round trips as the query is already on the server.
   
   Statements in functions and procedures don't get planned until the function
   or procedure is called for the first time.  These plans don't get cached unless
   the procedural language you are using has special support for that.
   
   Currently, only functions and procedures written in PL/pgSQL cache execution
   plans of static SQL statements.  And you are right, that is usually a good thing.
   
   Yours,
   Laurenz Albe
   
   
   
   

7. Re: Prepared statements versus stored procedures

   Merlin Moncure <mmoncure@gmail.com> — 2023-11-21T00:26:32Z

   On Mon, Nov 20, 2023 at 4:07 AM Laurenz Albe <laurenz.albe@cybertec.at>
   wrote:
   
   > On Sun, 2023-11-19 at 17:30 +0000, Simon Connah wrote:
   > > I was reading about prepared statements and how they allow the server to
   > > plan the query in advance so that if you execute that query multiple
   > times
   > > it gets sped up as the database has already done the planning work.
   > >
   > > My question is this. If I make a stored procedure doesn't the database
   > > already pre-plan and optimise the query because it has access to the
   > whole
   > > query? Or could I create a stored procedure and then turn it into a
   > prepared
   > > statement for more speed? I was also thinking a stored procedure would
   > help
   > > as it requires less network round trips as the query is already on the
   > server.
   >
   > Statements in functions and procedures don't get planned until the function
   > or procedure is called for the first time.  These plans don't get cached
   > unless
   > the procedural language you are using has special support for that.
   >
   > Currently, only functions and procedures written in PL/pgSQL cache
   > execution
   > plans of static SQL statements.  And you are right, that is usually a good
   > thing.
   >
   
   Adding to this,
   Stored procedures and functions can provide really dramatic speedups by
   eliminating round trips between statements where with traditional
   programming approaches you have to bring all the data back to the client
   side just to transform, run logic, etc, only to send it back to the
   database.  How much benefit this provides is really situation specific, but
   can be impactful in many common situations.
   
   Also, they provide the benefit of hiding schema details and providing a
   "database API" in situations where you want the application contract to the
   database to be written against the query output (perhaps in json) vs the
   schema.  This pattern is controversial in some circles but I employ it
   often and it runs well.  It can also be comforting not to rely on client
   side code to properly frame up the transaction.
   
   This is only touching the surface -- there are many, many advantages to
   server side programming and it is a tremendously valuable skill to learn
   and master.  The main downside, of course, is that postgres server
   programming can only be used in postgres without modification.
   
   merlin