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. Monday, November 9, 2015 at 7:11:48 PM UTC+1, Sean Corfield wrote: > > 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 > > > > (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"])))) > > > > On Monday, November 9, 2015 at 11:45:48 AM UTC+1, Niels van Klaveren wrote: >> >> While answering this question >> <http://stackoverflow.com/questions/33600132/checking-if-uuid-exists-in-postgresql-table-using-clojure-java-jdbc-db-do-prepar/33605673#33605673> >> >> 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 ? >> >> -- > 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 <javascript:> > 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 <javascript:> > 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 <javascript:>. > For more options, visit https://groups.google.com/d/optout. > > -- You received this message because you are subscribed to the Google Groups "Clojure" group. To post to this group, send email to clojure@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+unsubscr...@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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
