Hi Sanjay,
On 19 Apr 2021, at 18:14, Sanjay Minni <sm@planage.com> wrote:
Hi
using the P3 Postgres driver
what is the optimised way of using the following in a seaside/web application statement := client prepare: ... statement execute: statement close.
Sven readme on the drivers page "prepared statements ... need to be closed, prepared statement exist ... single session / connection ..."
First make sure that you give each session in your (seaside) web application its own connection/client to the database. P3Client is meant to be used single threaded, it is your responsibility to protect this. Make sure these are initialised and disposed of properly - use logging. Second, I would not put time in trying to gain performance with prepared statements until you can prove that it makes a real, measurable difference. PostgreSQL is very fast. But this is IMHO. See further.
Typically in a desktop app I would fire the "prepare" statements(s) once when open the particular UI / window (say typically 2 or 3 statements in a UI) and then "close" when I exit
How does this work in a multi-tabbed browser app lets say I open a tab with a particular UI and fire the "prepare" statement
now what if the tab idles for too long - and then i press send/save. The program would have just fired the "execute ..." assuming the prepare is active. would the prepare / session have been automatically closed ?
- when should i typically fire the prepare statement
Either upfront when you connect, or each time when you need them (see further). The scope of a prepared statement is the connection/session. Preparing 10s of statements upfront that you might not need could increase connection time.
- how should i test if the session / prepare is still active or needs to be refired
There is P3Client>>#preparedStatementNamed: You best use P3Client>>#prepare:named: then.
- how are orphan prepare statements disposed off ny the database / program
They get thrown out when the connection closes. HTH, Sven PS: There is also P3ConnectionPool that can protect against concurrent access, prepare connections, warm up, on top of its base functionality of pooling, of course. But a challenge with connection pooling is what to do when errors occur.
thanks for pointers Sanjay
----- cheers, Sanjay -- Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html