Re: clojure.java.jdbc prepared select statement

[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


-- 
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.

Re: clojure.java.jdbc prepared select statement

[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:
>
> 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 ?
>
>

-- 
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.

Re: clojure.java.jdbc prepared select statement

[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



(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 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 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.

-- 
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.

Re: clojure.java.jdbc prepared select statement

[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"]



On Monday, November 9, 2015 at 11:45:48 AM UTC+1, Niels van Klaveren wrote:
>
> 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 ?
>
>

-- 
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.

Re: clojure.java.jdbc prepared select statement

[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.

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 
>> 
>>  
>> 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 
> 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.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 ?

-- 
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.