clojure.java.jdbc prepared select statement

[Niels van Klaveren]

While answering this question on stackoverflow about do-prepared I wanted to show the way to do multiple queries with the same prepared statement (batch select). I thought this is possible in Java, but in clojure.java.sql I ran into several problems. Granted that given a properly parameterized query the database should be able to optimize this automatically by using the execution plan from the cache, it might give an extra bit of performance by stating it up front.
So I set out to test if that was true, but without much success

Here's my first attempt:

(j/with-db-connection[c datasource]
                 (let [ps (j/prepare-statement c "SELECT count(*) from person where left(name,1)=?")]
                   (doall (map #(j/query c [ps %]) ["a" "b"]))))
ClassCastException clojure.lang.PersistentHashMap cannot be cast to java.sql.Connection  clojure.java.jdbc/prepare-statement (jdbc.clj:454)

This is because prepare-statement seems to expect a true connection, not the datasource map. All other attempts to wrangle a connection into with-db-connection ended up in other errors.

(j/with-db-transaction [c (j/get-connection datasource)]
                 (let [ps (j/prepare-statement c "SELECT count(*) from person where left(name,1)=?")]
                   (doall (map #(j/query c [ps %]) ["a" "b"]))))
IllegalArgumentException No implementation of method: :get-level of protocol: #'clojure.java.jdbc/Connectable found for class: com.microsoft.sqlserver.jdbc.SQLServerConnection  clojure.core/-cache-protocol-fn (core_deftype.clj:544)

Since query accepts a prepared statement instead of a string, I thought this should be possible without having to implement a query equivalent of do-prepared.

Is there something I overlooked ?

[Niels van Klaveren]

OK, so after some more experimentation I found out I needed to do the get-connection in the prepare-statement instead of the with-db-connection binding. A bit counter-intuitive, and I hope it will still work when a connection pool is used.

(j/with-db-connection [c datasource]
                      (let [ps (j/prepare-statement (j/get-connection c)

                                                    "SELECT count(*) from person where left(name,1)=?")]
                        (doall (map #(j/query c [ps %])

                                    ["a" "b" "c" "d" "e" "f"]))))

[Niels van Klaveren]

Performance wise the conclusion is batch queries done this way are around 60% faster than doing separate queries for even very simple queries.

On Monday, November 9, 2015 at 11:45:48 AM UTC+1, Niels van Klaveren wrote:

[Sean Corfield]

Niels van Klaveren wrote on Monday, November 9, 2015 at 7:20 AM:

OK, so after some more experimentation I found out I needed to do the get-connection in the prepare-statement instead of the with-db-connection binding. A bit counter-intuitive, and I hope it will still work when a connection pool is used.

Yes, with-db-connection gives you a db-spec with a DB connection added which is specifically what let’s you use get-connection on it to retrieve the existing connection. Prepare-statement accepts a connection, not a db-spec.

Suggestions to improve docstrings are welcomed, as are Pull Requests to improve the community-maintained documentation here:

http://clojure-doc.org/articles/ecosystem/java_jdbc/home.html

Sean

--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clo...@googlegroups.com
Note that posts from new members are moderated - please be patient with your first post.
To unsubscribe from this group, send email to
clojure+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
---
You received this message because you are subscribed to the Google Groups "Clojure" group.
To unsubscribe from this group and stop receiving emails from it, send an email to clojure+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Niels van Klaveren]

Thanks for the explanation Sean, that makes a whole lot more sense to me now. I knew I had to miss something when I saw query would also accept prepared statements!
In no way did I want to criticize your awesome library, and I'll see if I can expand on the documentation.

The use of prepared statements in queries was pretty academic to me, since most queries I generate do not use much repetition. After all, working in sets is the forte of SQL, and repeating queries is something of an anti-pattern. However, I was very surprised seeing the performance differences in queries. On selecting 100K of separate unids on SQL server it was 55 seconds vs. 33 seconds using query vs. reused prepared query, and only 360ms of these 55s were spent in prepare-statement. The result set processing was identical. I had always assumed normal execution plan caching for parametrized queries would be almost just as effective, but 60% is no small difference.

I'll definitely keep this in mind whenever I do need to repeat queries and transactions.

[Sean Corfield]

In no way did I want to criticize your awesome library

None taken!

I'll see if I can expand on the documentation.

Thank you! I’m not great at documentation, never have been (one of my first jobs, they hired a technical writer to turn my attempt at documentation into something usable…), so I’m always very grateful when the community submit improvements to any documentation I’ve provided.

However, I was very surprised seeing the performance differences in queries. On selecting 100K of separate unids on SQL server it was 55 seconds vs. 33 seconds using query vs. reused prepared query, and only 360ms of these 55s were spent in prepare-statement. The result set processing was identical. I had always assumed normal execution plan caching for parametrized queries would be almost just as effective, but 60% is no small difference.

I’m not actually too surprised at this. It’ll be very dependent on your database, your configuration, your driver, and your particular query. Unfortunately.

Sean