On Sunday, 19 November 2023 at 18:09, Francisco Olarte <fola...@peoplecall.com> wrote: >
> > Hi Simon: > > On Sun, 19 Nov 2023 at 18:30, Simon Connah > simon.n.con...@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.
signature.asc
Description: OpenPGP digital signature
