Any tips about tuning the SAS JDBC performance

Minghe — Thu, 09 Jan 2014 09:48:12 GMT

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();

Re: Any tips about tuning the SAS JDBC performance

PaulHomes — Fri, 26 May 2017 00:21:32 GMT

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.

topic Any tips about tuning the SAS JDBC performance in SAS Data Management

Any tips about tuning the SAS JDBC performance

Re: Any tips about tuning the SAS JDBC performance