Fork me on GitHub
#sql
<
2019-07-08
>
Jakub Holý (HolyJak)18:07:56

@seancorfield Does next.jdbc support batched updates? I see it has insert-multi but no update-multi?

seancorfield19:07:05

clojure.java.jdbc has no batched update. next.jdbc.sql contains a few "syntactic sugar" commands that mimic clojure.java.jdbc to make migration easier.

seancorfield19:07:32

(in other words, there's never been any equivalent to update-multi!)

seancorfield19:07:01

@holyjak I'm not even sure what a "batched update" would look like 🙂

seancorfield19:07:50

next.jdbc doesn't call .addBatch at all because it uses the generic .execute call to ensure consistent semantics across all functions (unlike clojure.java.jdbc).

seancorfield19:07:56

next.jdbc allows you to execute "any" SQL (via plan, execute-one!, and execute!) so that's what I'd advise for whatever you're trying to do.

Jakub Holý (HolyJak)19:07:35

I assume those call Statement.execute, which is not the same as Statement.addBatch ... executeBatch. In my experience, batching can have very important effect on the performance. Or am I missing something?

seancorfield19:07:02

I don't know whether you're missing anything... insert-multi! was originally intended to support fast multi-row insertion but as I found out, didn't actually do a single batch operation on some DBs unless a "rewrite SQL" option was also specified (a DB-specific option: PG and MySQL use a different option for this).

seancorfield19:07:05

You could do a single INSERT via execute! in clojure.java.jdbc or next.jdbc that would behave like the (rewritten) batch-based SQL for insert-multi! -- since it is purely about the SQL underneath for those DBs.

seancorfield19:07:43

I would assume you could do the same with UPDATE?

Jakub Holý (HolyJak)19:07:54

I just looked at the code and discovered that, as you describe, -multi just concatenates strings.

seancorfield19:07:05

Searching for batch updates indicates that MySQL doesn't support them, so I guess this is DB-specific?

Jakub Holý (HolyJak)19:07:16

As I mentioned, actually using JDBC batching facilities is quite important sometimes, shouldn't there be a support for it? As you discovered some DBs do not support them but that should not matter, if they don't then JDBC should just issue a series of individual executes (I believe) so there should be no harm in using batching.

seancorfield19:07:25

(was reading java.sql docs)

Jakub Holý (HolyJak)19:07:40

I was wrong, executeBatch may throw if the driver does not support batching

Jakub Holý (HolyJak)19:07:00

I guess I can do this myself using next.jdbc.prepare/create and set-parameters and then manually calling .addBatch and .executeBatch on the prepared statement.... Still, wouldn't it be worth having built-in support? I'd be happy to help with that.

seancorfield19:07:56

So there are two types of batching in SQL: a batch of commands (which clojure.java.jdbc does in db-do-commands -- and next.jdbc does not), and a batch of parameters on a PreparedStatement, which has DB-specific behavior and can be rewritten to perform a single SQL operations instead.

Jakub Holý (HolyJak)19:07:36

Aside: create (https://github.com/seancorfield/next-jdbc/blob/v1.0.1/src/next/jdbc/prepare.clj#L74) would benefit from an example or mentioning that an example is in the guide (for the bad, bad ppl that come to the apidocs w/o reading the guide first ;-))

seancorfield19:07:47

Feel free to open an issue on GitHub to discuss this further but I think it would need to be layered on as a new API (parameter batching).

👍 4
seancorfield19:07:26

Re: create -- use next.jdbc/prepare to create PreparedStatements. I'll make that clearer in the docstring (that create should not be called).

👍 4
Jakub Holý (HolyJak)19:07:34

Are you sure about > PreparedStatement, which has DB-specific behavior and can be rewritten to perform a single SQL operations instead. i.e. are Statement.add/executeBatch and PreparedStatement.add/executeBatch so different from each other? I would expect the JDBC driver to handle both quite similarly. (2) Not sure what > can be rewritten to perform a single SQL operations means - you mean the programmer could replace it with writing a very long SQL? If that is the case, isn't she likely to run into statement size limits that batching handles better?

seancorfield19:07:39

I've just added this to the create docstring: This is an implementation detail -- use next.jdbc/prepare` instead."

seancorfield19:07:10

Like I said: open a GitHub issue to discuss in more detail. Given the issues with executeBatch that I've seen over the years with clojure.java.jdbc, I need convincing with specific examples and benchmark/timing details.

👍 4
seancorfield19:07:32

As for the SQL rewrite, I don't know the details: it was first reported against PostgreSQL (which I don't use) and the rewrite option was suggested as a fix, then it was confirmed against MySQL too, which needs a different option to be provided. So the bottom line there is addBatch/`executeBatch` does not do what people think on those two popular DBs and, as you have just confirmed for yourself, the "batch" commands throw exceptions when they can't be used, so generic JDBC code cannot use them.

seancorfield19:07:30

If you can convince me that a new set of functions needs to be added, specifically to deal with the batched parameters case, because it cannot be done with the regular execute path, I'll dig in more seriously.

seancorfield19:07:07

(and this was all part of the problems people had with clojure.java.jdbc because different paths through the library used different SQL methods)

Jakub Holý (HolyJak)20:07:14

@seancorfield I would very much appreciate an example of how to use a prepared statement. I know how to do that in Java - ps = Connection.prepareStatement("INSERT/UPDATE ... ? ...") -> ps.set*(idx, value) -> execute. next.jdbc/prepare doesn't take just a string as prepareStatement but also params - what params? And where does set-parameters enter the game? Is it so that prepare combines .prepareStatement + .set* and set-parameters is needed for 2nd+ reuse of the prep. stmt?

seancorfield20:07:51

Does that not answer your question? If not, how can it be improved?

Jakub Holý (HolyJak)20:07:57

Well, upon 2nd reading it does. It could be improved with a more concrete example, such as

(with-open [ps (jdbc/prepare con ["INSERT into employees(id,name) values(?,?)" 1 "John"])]
    (execute-one! ps nil {...})
    (next.jdbc.prepare/set-parameters ps [2 "Bob"])
    (execute-one! ps))

seancorfield20:07:05

There's also this test https://github.com/seancorfield/next-jdbc/blob/master/test/next/jdbc_test.clj#L95-L99 which provides no parameters in the prepare call and uses set-parameters later.

seancorfield20:07:42

OK, I can add an example in that doc page.

Jakub Holý (HolyJak)20:07:26

hm, I have not figured out at all that I could do what this latest example does, i.e. call prepare with no params. That could be clarified for slower ppl like me as well 🙂

Jakub Holý (HolyJak)20:07:19

Have you mentioned that you are awesome? ❤️ 🙂

😊 4
Jakub Holý (HolyJak)20:07:38

I wonder, why do you use nested with-open and not a single one with multiple bindings? If I read the source code right, it does rewrite itself into multiple nested with-opens so you don't need to do that manually???

noisesmith20:07:22

right, with-open clauses can refer to bindings from preceding clauses

seancorfield21:07:11

I think it's clearer for documentation purposes -- and you might get a connection and hold it across multiple operations that each opened a new prepared statement...

seancorfield21:07:43

You only need multiple bindings or nested calls when you're using raw prepared statements or you're running transactions on an already open connection which aren't mainstream use cases...

seancorfield21:07:16

Feel free to open a GitHub issue to suggest alternatives in the docs.

Jakub Holý (HolyJak)21:07:41

No, it is OK, I was just surprised and thought that might be some execution-wise reason for this, afraid there is something important I missed.