Insert statements do not seem to use JDBC batch functionality

[cbenning]

Rather than generate a short insert statement:

INSERT INTO mytable (col1,col2) VALUES ("asd","asd");

and then iterating over a list of records, using the JDBC ".addBatch" method to add each one. Korma seems to be generating one large SQL statement:

INSERT INTO mytable (col1,col2) VALUES ("asd","asd"), ("asd","asd"),.... ;

And then flattening the 2d seq of arguments which it then adds as a single batch. As I attempt to add batch inserts for Vertica, and Vertica does not support the syntax:

INSERT INTO mytable (col1,col2) VALUES ("asd","asd"), ("asd","asd"),.... ;

I stumbled across this, as I realized from what I can tell, the 'exec-sql' function in 'db.clj' does not seem to run the correct JDBC function for a 2d seq, instead using a single large sequence. I'm not overly familiar with the way Korma works internally, and would appreciate some guidance/input from someone who knows better.

What is the reason Korma does this? It is not conducive to adding support for other DBs.

[immoh]

I think the reason for preferring single insert statement with multiple rows to multiple insert statements is simply better performance. I wasn't aware that there are DBs that don't support multi-row insert statements. As this works fine with most of the DBs, I don't see that it should be changed but I wouldn't object to adding support for .addBatch to Korma.

[holyjak]

I think that using batch updates would be a better solution because 1) that is what batch updates are for and 2) it allows control over batch size (it does not really help when the batch is too large, in my experience - depends on the DB in question).