Any tips about tuning the SAS JDBC performance

[Minghe]

As title. Especially the insert performance of SAS JDBC is terrible when I'm writing a JDBC application by Java.

What I did was prepare an insert statement, then pass to the JDBC connection (SAS 9.4 IOM driver, driver class com.sas.rio.MVADriver).

I observed that SAS treated statement.executeBatch() / statement.execute() the same. Seems batch execution won't have performance gains. Tried to change the fetch size which almost the same. Suspended SAS log helped a little. But it's still very slow.

Deeply appreciated if somebody can share the tips for optimizing the SAS JDBC insert.

Attached the extraction of java code for discussion.

prepare and execute insert statements with SAS JDBC
while (rs.next()) { rows++; for (int i = 1; i <= columns.size(); i++) { converter.prepareInsertSql(rs, statement, i); } statement.addBatch(); if ((rows % fetchSize) == 0) { statement.executeBatch(); statement.clearWarnings(); target.commit(); } } statement.executeBatch(); target.commit();

[PaulHomes]

Whilst this question is several years old now, I stumbled on it while investigating slow performance when using the SAS JDBC driver for lots of updates. For the benefit of others who may find this thread in future, I'll add my own findings too. Like the original poster I found that using JDBC batch didn't help much. In my case I found it was significantly faster to handle my own batching and spawn a workspace server session to submit proc sql code to do a batch of inserts/updates instead.