SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Any tips about tuning the SAS JDBC performance

Reply
N/A
Posts: 1

Any tips about tuning the SAS JDBC performance

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();
PROC Star
Posts: 392

Re: Any tips about tuning the SAS JDBC performance

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.

Ask a Question
Discussion stats
  • 1 reply
  • 396 views
  • 2 likes
  • 2 in conversation