Clojurians Log v2

The choice is mostly between execute! and plan -- if you want a single item as a hash map, execute-one! is probably your best bet.

execute! produces a full vector of hash maps. All the rows from the (Java) ResultSet are converted into Clojure hash maps, and a vector is built and returned. Then you perform whatever processing you want on that vector.

plan is intended to be used with reduce / transduce to apply a process to each row in turn, and to accumulate some sort of result (could be anything, including a vector). The process is able to directly access column values from the ResultSet without Clojure hash maps being built. The ResultSet can be larger than would fit in memory as a Clojure vector of Clojure hash maps -- it can be made to "stream" from the database in chunks. The process is able to abort at any point with reduced.

The better performance comes from not converting the whole ResultSet into a Clojure vector of Clojure hash maps.

The short answer to "when to use it" is "all the time" 🙂 But for small result sets, it probably doesn't make enough of a difference for you to change from (map my-process (jdbc/execute! ...)) to (into [] (map my-process) (jdbc/plan ...)) or a reduce etc.

If you just want a sequence of, say, ID columns from a bunch of rows, next.jdbc.plan/select! is an nice, efficient way to get that vector of values. It uses plan under the hood to avoid constructing all those Clojure hash maps.

You can see how select! works here https://github.com/seancorfield/next-jdbc/blob/develop/src/next/jdbc/plan.clj#L66-L70 and that might give you an idea of how you'd want to use plan instead of execute!...

(note that the vector cols branch does create Clojure hash maps -- but the other branch can avoid that depending on what cols is: a single column name -- no map construction; a function -- if it just "gets" columns from the row passed in then the overhead is only what it does with those column values)

Hmm ok very interesting. So is the only reason to do the query like: (plan/select! ds :id ["select * from table"]) instead of (jdbc/execute! ds ["select id from table"]) so it doesn't actually perform the conversion from java data structures to Clojure data structures and instead it would read the id value directly from the java result set and construct the resulting vector like that? I'm trying to understand why the select * in plan would be more efficient than doing select id directly in execute or even in a plan.

Also are you basically saying anywhere we are using execute to get multiple rows we should just use plan instead?

You'd still want to do select id.

Oh ok I was looking at the doc string and you had (plan/select! ds :id ["select * from table"])

The difference is that execute! produces [ {:table/id 1} {:table/id 2} .. ] (or just :id if you use the unqualified builder), but the select! produces [ 1 2 .. ]

(into [] (map :id) (jdbc/plan ds ["select id from table"])) is the same as (plan/select! ds :id ["select id from table"])

Ok so you are saying (plan/select! ds :id ["select id from table"]) will be faster than doing (jdbc/execute! ds ["select id from table"]) . What about for queries that are selecting multiple keys using select *?

They do not return the same thing.

plan/select! will return a vector of the ID column values. jdbc/execute! will return a vector of hash maps, each of which has the key :id (or :table/id by default) and the corresponding value.

Yeah I understand but effectively its the same thing

No, it's not.

That's the point.

Meaning represents the same data though

With jdbc/execute! you get a fully-formed Clojure data structure that you then subsequently process. With jdbc/plan you get a reducible collection (that hasn't done any work) and then you reduce (or transduce) it to run the query and then you can get the raw column values from the ResultSet without building Clojure data structures.

I guess, with the way this thread is going, I should take a step back and ask: How comfortable are you with transducers?

I'm wondering if you don't "get" plan because you haven't used transducers much...?

I only really have experience using Clojure functions like filter, map, reduce etc. I haven't used the transduce function before nor do I know why should I.

Ah, OK, that's probably why the difference between execute! and plan seems hazy then...

Yeah sorry about that lol. Seems I have some more learning to do. Thanks though