Re: Prepared statements versus stored procedures

On Sunday, 19 November 2023 at 18:09, Francisco Olarte <folarte(at)peoplecall(dot)com> wrote:
>

>

> Hi Simon:
>

> On Sun, 19 Nov 2023 at 18:30, Simon Connah
> simon(dot)n(dot)connah(at)protonmail(dot)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.