Re: Prepared statements versus stored procedures

On Mon, Nov 20, 2023 at 4:07 AM Laurenz Albe wrote: > On Sun, 2023-11-19 at 17:30 +, 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 > >

Re: Prepared statements versus stored procedures

On Sun, 2023-11-19 at 17:30 +, 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. > >

Re: Prepared statements versus stored procedures

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 op

Re: Prepared statements versus stored procedures

On Sunday, 19 November 2023 at 18:09, Francisco Olarte 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 th

Re: Prepared statements versus stored procedures

Hi Simon: On Sun, 19 Nov 2023 at 18:30, 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.

Re: Prepared statements versus stored procedures

less 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.

Prepared statements versus stored procedures

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

Re: Prepared statements plan_cache_mode considerations

On Thu, Oct 26, 2023 at 10:56 AM Zain Kabani wrote: > I was looking into using prepared statements and using the auto > plan_cache_mode. The issue is that the current sample of data collected to > determine whether to use custom plans or the generic one is very small and > suscepti

Prepared statements plan_cache_mode considerations

Hi team, I was looking into using prepared statements and using the auto plan_cache_mode. The issue is that the current sample of data collected to determine whether to use custom plans or the generic one is very small and susceptible to a bad set of queries that might pick the suboptimal choice

Re: PL/pgSQL's "open cur for execute" provides workarouned... Re: cursors with prepared statements

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> It seems that there must be different underlying mechanisms at work and that >> this explains why creating a cursor using SQL to execute a prepared >> statement fails but doing this using PL/pgSQL succeeds. What's going on

Re: PL/pgSQL's "open cur for execute" provides workarouned... Re: cursors with prepared statements

On 4/16/23 11:02, Bryn Llewellyn wrote:> statement fails but doing this using PL/pgSQL succeeds. What's going on under the covers? Pretty sure: https://www.postgresql.org/docs/current/spi.html -- Adrian Klaver adrian.kla...@aklaver.com

PL/pgSQL's "open cur for execute" provides workarouned... Re: cursors with prepared statements

> b...@yugabyte.com wrote: > >> david.g.johns...@gmail.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> I found this email from Peter Eisentraut: >>> https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com >>> >>> It caused the 42601 error, « syntax error

Re: cursors with prepared statements

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> I found this email from Peter Eisentraut: >> https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com >> >> It caused the 42601 error, « syntax error at or near “execute” ». So it >> look

Re: cursors with prepared statements

On Sat, Apr 15, 2023 at 2:15 PM Bryn Llewellyn wrote: > I found this email from Peter Eisentraut: > > https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com > > It caused the 42601 error, « syntax error at or near “execute” ». So it > looks like Peter’s patch

Re: cursors with prepared statements

I found this email from Peter Eisentraut: https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com > I have developed a patch that allows declaring cursors over prepared > statements... This is an SQL standard feature. ECPG already supports it

Does the postgres jdbc driver (rev 42.3) cache prepared statements

I have an embedded tomcat talking directly to postgres server via sql generated by jOOQ.  By default jOOQ creates a prepared statement for all selects, and does so on each invocation from the client (client in this case is a servlet in tomcat).  I've been using jOOQ-to-db for a while but the se

psycopg3: prepared statements

Hello, I am gathering ideas about how to implement prepared statements in psycopg3. A plan is sketched at <https://github.com/psycopg/psycopg3/discussions/21>. Feedback is welcome, thank you very much! -- Daniele

Re: query logging of prepared statements

On Fri, Feb 08, 2019 at 07:29:53AM -0600, Justin Pryzby wrote: > A couple months ago, I implemented prepared statements for PyGreSQL. While > updating our application in advance of their release with that feature, I > noticed that our query logs were several times larger. Previousl

query logging of prepared statements

A couple months ago, I implemented prepared statements for PyGreSQL. While updating our application in advance of their release with that feature, I noticed that our query logs were several times larger. With non-prepared statements, we logged to CSV: |message| SELECT 1 With SQL

Re: Prepared statements

> You mean Oracle 11g. No 12c. Some of it may have started in 11g itself, but only in 12c they really mastered it. I saw it as a developer, not as a DBA. I was never an oracle DBA.

Re: Prepared statements

Rakesh Kumar wrote: > Only in Oracle 12c there are > options to let the planner change existing plan by peeking into the parameter > values (supplied in ? of prepare) and checking it against the distribution. You mean Oracle 11g. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql

Re: Prepared statements

> On Mar 21, 2018, at 2:09 PM, Tim Cross wrote: > > > a simple question I wasn't able to get a clear answer on > > It is general best practice to use prepared statements and parameters > rather than concatenated strings to build sql statements as mitigati

Re: Prepared statements

> For example, the planner may be able to > more easily recognise a statement and reuse an existing plan rather than > re-planning the query. This is a double edged sword. Reuse an existing plan can be bad in those cases where the data distribution is not suitable for the current plan. This has b

Prepared statements

a simple question I wasn't able to get a clear answer on It is general best practice to use prepared statements and parameters rather than concatenated strings to build sql statements as mitigation against SQL injection. However, in some databases I've used, there is also a p

Re: Prepared statements (PREPARE and JDBC) are a lot slower than "normal" ones.

Robert Zenz wrote: > We are seeing a quite heavy slow down when using prepared statements in 10.1. > > I haven't done some thorough testing, to be honest, but what we are having is > a > select from a view (complexity of it should not matter in my opinion), >

Prepared statements (PREPARE and JDBC) are a lot slower than "normal" ones.

We are seeing a quite heavy slow down when using prepared statements in 10.1. I haven't done some thorough testing, to be honest, but what we are having is a select from a view (complexity of it should not matter in my opinion), something like this: prepare TEST (text, int) select *