sql 2023-02-15 | Slack Archive

Hanan Benzion22:02:23

A question regarding connection pooling and PreparedStatement caching I am trying to optimize my SQLite queries by working with connection pooling and with a prepared statement using jdbc/prepare and I’m getting an Error saying this: :cause No implementation of method: :prepare of protocol: #'next.jdbc.protocols/Preparable found for class: com.zaxxer.hikari.HikariDataSource My use case - My app runs the exact same query, but only with a different value for the same indexed column. I was working with the HikariCP. I can try work with c3p0 but I’m trying to understand if it is possible to do it with HikariCP as well and also looking to understand this better. Looking at the next.jdbc docs I couldn’t see that there is a limitation but it say that the prepare is meant to work https://cljdoc.org/d/seancorfield/next.jdbc/1.2.659/doc/getting-started/prepared-statements#:~:text=If%20you%20have%20a%20SQL%20operation%20that%20you%20intend%20to%20run%20multiple%20times%20on%20the%20same%20java.sql.Connection%2C%20it%20may%20be%20worth%20creating%20the%20prepared%20statement%20yourself%20and%20reusing%20it., that mean it won’t work with connection pooling? On the other hand, in the HikaryCP docs it say that it doesn’t offer https://cljdoc.org/d/seancorfield/next.jdbc/1.2.659/doc/getting-started/prepared-statements#:~:text=If%20you%20have%20a%20SQL%20operation%20that%20you%20intend%20to%20run%20multiple%20times%20on%20the%20same%20java.sql.Connection%2C%20it%20may%20be%20worth%20creating%20the%20prepared%20statement%20yourself%20and%20reusing%20it. and that it should be done in the driver level. Later on, it has some https://github.com/brettwooldridge/HikariCP#statement-cache:~:text=You%20can%20use%20the%20HikariConfig%20class%20like%20so1%3A that seem to be related to allowing prepared statement in initialization so I wasn’t sure if that can work and how 😐 So I got confused by how I should actually make this work together, any help on that will be much appreciated 🙂 Thanks

hiredman22:02:26

you are calling prepare on the datasource (the connection pool) not a connection from the pool

hiredman23:02:37

using prepared statements is going to be of questionable utility with a connection pool, because typically the way you use a connection pool is you take a connection from it for some short amount of time then return it, then letter do the same again, likely getting a different connection object from the pool

hiredman23:02:17

but prepared statements are generally tied to a given connection

hiredman23:02:01

unless you are doing complex queries (that result in a complex query plan, lots of joins, multiple indices, etc) then it is unlikely that a prepared statement will get you much anyway

seancorfield23:02:47

(and the documentation explains that you call prepare on a Connection only)

seancorfield23:02:41

So you get a Connection from the pool, prepare a statement on it, and reuse that that PreparedStatement with various parameters (passing it into execute! etc). And then you close the PreparedStatement and the Connection when you're done (`with-open` is your friend there).

seancorfield23:02:37

This page of the docs has some examples: https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.847/doc/getting-started/prepared-statements

Hanan Benzion09:02:00

#Also sent to the channel

@seancorfield How would you do the following: • I have a service with connection pooling where my service runs the exact single query with different values to one parameter only (it’s also the primary key). • I have thousands of requests per second that do the exact same query with different values of the parameter above. I assume that I can benefit from performance improvement by working with Prepared statement. I understand that the below code take a connection and at the end returns it to the connection pooling (with-open [con (jdbc/get-connection ds)] …) In the documentation, I see that with-open is used also for the statement and than we execute multiple command. How can I save the statement “attached” to that connection without running it again and again? From the documentation: https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.847/doc/getting-started/prepared-statements#:~:text=It%20is%20your%20responsibility%20to%20close%20the%20prepared%20statement%20after%20it%20has%20been%20used I would want my preparedStatement to be “attached” to a connection all of it’s lifetime without running it for every execute-one My code now:

(defonce ^:private db {:dbtype "sqlite" :dbname db-name})

(defonce ^:private ds (connection/->pool HikariDataSource db))

(defn get-single-result-by-key
  [value]
  (with-open [con (jdbc/get-connection ds)]
    (let [ps (jdbc/prepare con [(str "SELECT * FROM table1 WHERE id= ? LIMIT 1")])]
      (jdbc/execute-one! ps [value]
                         {:builder-fn rs/as-unqualified-lower-maps}))))

My service run the get-single-result-by-key function for every request. How would you do that? Posting it also in channel is it can be useful for more people. Thanks

seancorfield17:02:35

#Also sent to the channel

The example in the documentation shows this:

;; assuming require next.jdbc.prepare :as p
(with-open [con (jdbc/get-connection ds)
            ps  (jdbc/prepare con ["..."])]
  (jdbc/execute-one! (p/set-parameters ps [...])))

seancorfield17:02:13

I don't think a single Connection is going to be thread-safe in terms of supporting multiple concurrent threads using it at the same time (i.e., multiple separate requests).

seancorfield17:02:42

So you have to have at least one Connection per thread (per request).

Hanan Benzion17:02:00

I want to use connection pool for sure, I just wonder how can I benefit the prepared statement with all the connections.

Hanan Benzion17:02:28

In other words, how can I preserve and reuse the single statement that can be used by all connections for all the requests?

seancorfield17:02:10

That's not possible. A PreparedStatement "belongs" to a Connection. That's just how JDBC works.

hiredman17:02:41

how complicated is your query though?

hiredman17:02:54

a prepared statement caches the execution plan that the sql engine constructs for a given query, if your query is a simple primary key lookup, generating the execution plan is likely cheap enough not to matter

Hanan Benzion17:02:32

@U0NCTKEV8 very simple query. Just asking for object with different values of my pk. I’m just trying to optimize the queries to the maximum.

seancorfield17:02:47

You're optimizing before you've proven you have a performance problem.

hiredman17:02:01

if you tie your architecture into knots to serve the ability to use preparedstatements, then preparedstatements better be a significant performance boost for you

seancorfield17:02:13

If you have very lightweight requests that mostly just SELECT a single row by pk, you are only going to be able to speed that up by caching in front of the JDBC machinery, e.g,. Redis.

seancorfield17:02:45

(using JDBC for in-memory DBs has always seemed a bit strange to me since JDBC itself is pretty "heavy" machinery)

Hanan Benzion17:02:57

Thanks for the input. As I mentioned in earlier messages, the in-memory DB in my use-case have some other uses as well so trying to optimize it as an in process data store.

hiredman17:02:15

have you considered using something that is not sqlite? sqlite is a native library, and there have been a few versions of jdbc drivers for it over the years, so I don't know what is available for it now, but the options have never been great for performance

hiredman17:02:34

there are java embedable sqls that may work better (apache derby and h2)

hiredman17:02:06

sqlite libraries may need to do a lot of jni (which introduces a certain amount of over head per call) or their used to be a jdbc sqlite library that compiled sqlite to mips instructions and then just write a mips emulator in java

seancorfield17:02:17

I mentioned H2 to @U03A86XSH52 in another thread just over a week ago. I'd certainly lean toward H2 (or Derby) rather than SQLite for JVM-based work.

seancorfield17:02:34

But I'd still want to consider some sort of in-memory k/v store to cache simple pk-based lookup if performance is that critical and staleness is not. Even org.clojure/core.cache (using the wrapped ns) and perhaps a TTL or LRU cache underneath.

👍 2

hiredman17:02:52

https://stackoverflow.com/a/62845028 mentions finding h2 to be much faster than sqlite, and if you are using the in memory store, the file corruption complained about shouldn't be an issue

👀 2

Hanan Benzion17:02:17

As I said, SQLite is used here for other purposes. I will maybe consider other solutions like H2 etc.. but for now I’m also trying to do the best possible effort with SQLite. Thanks for all the great info

Hanan Benzion09:02:00

replied to a thread:A question regarding `connection pooling` and `PreparedStatement caching` I am trying to optimize my SQLite queries by working with `connection pooling` and with a prepared statement using `jdbc/prepare` and I’m getting an Error saying this: `:cause No implementation of method: :prepare of protocol: #'next.jdbc.protocols/Preparable found for class: com.zaxxer.hikari.HikariDataSource` My use case - My app runs the exact same query, but only with a different value for the same indexed column. I was working with the `HikariCP`. I can try work with `c3p0` but I’m trying to understand if it is possible to do it with `HikariCP` as well and also looking to understand this better. Looking at the `next.jdbc` docs I couldn’t see that there is a limitation but it say that the prepare is meant to work https://cljdoc.org/d/seancorfield/next.jdbc/1.2.659/doc/getting-started/prepared-statements#:~:text=If%20you%20have%20a%20SQL%20operation%20that%20you%20intend%20to%20run%20multiple%20times%20on%20the%20same%20java.sql.Connection%2C%20it%20may%20be%20worth%20creating%20the%20prepared%20statement%20yourself%20and%20reusing%20it., that mean it won’t work with connection pooling? On the other hand, in the HikaryCP docs it say that it doesn’t offer https://cljdoc.org/d/seancorfield/next.jdbc/1.2.659/doc/getting-started/prepared-statements#:~:text=If%20you%20have%20a%20SQL%20operation%20that%20you%20intend%20to%20run%20multiple%20times%20on%20the%20same%20java.sql.Connection%2C%20it%20may%20be%20worth%20creating%20the%20prepared%20statement%20yourself%20and%20reusing%20it. and that it should be done in the driver level. Later on, it has some https://github.com/brettwooldridge/HikariCP#statement-cache:~:text=You%20can%20use%20the%20HikariConfig%20class%20like%20so1%3A that seem to be related to allowing prepared statement in initialization so I wasn’t sure if that can work and how :neutral_face: So I got confused by how I should actually make this work together, any help on that will be much appreciated :slightly_smiling_face: Thanks

@seancorfield How would you do the following: • I have a service with connection pooling where my service runs the exact single query with different values to one parameter only (it’s also the primary key). • I have thousands of requests per second that do the exact same query with different values of the parameter above. I assume that I can benefit from performance improvement by working with Prepared statement. I understand that the below code take a connection and at the end returns it to the connection pooling (with-open [con (jdbc/get-connection ds)] …) In the documentation, I see that with-open is used also for the statement and than we execute multiple command. How can I save the statement “attached” to that connection without running it again and again? From the documentation: https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.847/doc/getting-started/prepared-statements#:~:text=It%20is%20your%20responsibility%20to%20close%20the%20prepared%20statement%20after%20it%20has%20been%20used I would want my preparedStatement to be “attached” to a connection all of it’s lifetime without running it for every execute-one My code now:

(defonce ^:private db {:dbtype "sqlite" :dbname db-name})

(defonce ^:private ds (connection/->pool HikariDataSource db))

(defn get-single-result-by-key
  [value]
  (with-open [con (jdbc/get-connection ds)]
    (let [ps (jdbc/prepare con [(str "SELECT * FROM table1 WHERE id= ? LIMIT 1")])]
      (jdbc/execute-one! ps [value]
                         {:builder-fn rs/as-unqualified-lower-maps}))))

My service run the get-single-result-by-key function for every request. How would you do that? Posting it also in channel is it can be useful for more people. Thanks

seancorfield17:02:35

replied to a thread:A question regarding `connection pooling` and `PreparedStatement caching` I am trying to optimize my SQLite queries by working with `connection pooling` and with a prepared statement using `jdbc/prepare` and I’m getting an Error saying this: `:cause No implementation of method: :prepare of protocol: #'next.jdbc.protocols/Preparable found for class: com.zaxxer.hikari.HikariDataSource` My use case - My app runs the exact same query, but only with a different value for the same indexed column. I was working with the `HikariCP`. I can try work with `c3p0` but I’m trying to understand if it is possible to do it with `HikariCP` as well and also looking to understand this better. Looking at the `next.jdbc` docs I couldn’t see that there is a limitation but it say that the prepare is meant to work https://cljdoc.org/d/seancorfield/next.jdbc/1.2.659/doc/getting-started/prepared-statements#:~:text=If%20you%20have%20a%20SQL%20operation%20that%20you%20intend%20to%20run%20multiple%20times%20on%20the%20same%20java.sql.Connection%2C%20it%20may%20be%20worth%20creating%20the%20prepared%20statement%20yourself%20and%20reusing%20it., that mean it won’t work with connection pooling? On the other hand, in the HikaryCP docs it say that it doesn’t offer https://cljdoc.org/d/seancorfield/next.jdbc/1.2.659/doc/getting-started/prepared-statements#:~:text=If%20you%20have%20a%20SQL%20operation%20that%20you%20intend%20to%20run%20multiple%20times%20on%20the%20same%20java.sql.Connection%2C%20it%20may%20be%20worth%20creating%20the%20prepared%20statement%20yourself%20and%20reusing%20it. and that it should be done in the driver level. Later on, it has some https://github.com/brettwooldridge/HikariCP#statement-cache:~:text=You%20can%20use%20the%20HikariConfig%20class%20like%20so1%3A that seem to be related to allowing prepared statement in initialization so I wasn’t sure if that can work and how :neutral_face: So I got confused by how I should actually make this work together, any help on that will be much appreciated :slightly_smiling_face: Thanks

The example in the documentation shows this:

;; assuming require next.jdbc.prepare :as p
(with-open [con (jdbc/get-connection ds)
            ps  (jdbc/prepare con ["..."])]
  (jdbc/execute-one! (p/set-parameters ps [...])))

2023-02-15

Channels