Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Prepared statement gains #1805

Open
worekleszczy opened this issue Jan 17, 2023 · 1 comment
Open

Prepared statement gains #1805

worekleszczy opened this issue Jan 17, 2023 · 1 comment

Comments

@worekleszczy
Copy link

Hello,

going into database logs I was surprised to see that all queries are run as unnamed prepared statements.

E.g for a postgres database with a table:

CREATE TABLE users (
id SERIAL PRIMARY KEY,
name text NOT NULL,
surname text NOT NULL
);

and a statement:

def insert(name: String, surname: String): ConnectionIO[Int] = fr"INSERT INTO users(name, surname) VALUES ($name, $surname)".update.run

insert("NAME", "SURNAME").quick.unsafeRunSync

I can see in logs:

2023-01-18 00:02:40.006 CET [94971] LOG:  execute <unnamed>: BEGIN
2023-01-18 00:02:40.007 CET [94971] LOG:  execute <unnamed>: INSERT INTO users(name, surname) VALUES ($1, $2) 
2023-01-18 00:02:40.007 CET [94971] DETAIL:  parameters: $1 = 'NAME', $2 = 'SURNAME'
2023-01-18 00:02:40.010 CET [94971] LOG:  execute S_1: COMMIT

I tried to educate myself on unnamed prepared statements, but didn't find any useful information apart from that it's only support on the wire protocol. Can you tell me what gains does it provide? Can a database still remove planning a query part event though at a first glance it's not possible to distinguish one unnamed prepared statement from the other?

@jatcwang
Copy link
Collaborator

jatcwang commented Oct 9, 2023

If you're using Postgres JDBC (pgjdbc) driver, it already transparently created server-side prepared statement to cache commonly executed statements (This is scoped per DB connection so if you're using a connection pool it should just work).

Keen to see some benchmarks though if anyone wants to compare plain doobie vs reusing the same PreparedStatement object. (The linked docs do say that reusing the PreparedStatment object is more efficient, but I'm not sure by how much)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants