The performance of non-blocking jdbc

The JDK 9+ provided incubator to support non-blocking jdbc. The idea is trying to process data when reading or updating the database.  The code support most existing jdbc driver and just need minimal change of incubator code.
With the non-blocking jdbc code, the performance increased significantly for updating operations. But just hit slightly change on selection. It make sense since updating will cost on database side and our code will wait longer time to database IO response.

The chart clearly show performance comparison between block jdbc and non-block jdbc.

Overview, all of them hit the similar query time by access 10000 rows simple data.

When using just one thread, the non-block api just took half time of block api to update the database.

However, multiple thread increase the performance block api a lot. It should be like it since multiple thread used more system resources to process services. See the table before for detail data:

Although multiple thead hit the more efficient way to update the database,  it took more resources comparing with non-block jdbc 

See detail code for non-block jdbc

Query:

	private List<Integer> queryTestData(int start, int size) {
	String SQL = "select * from testdata OFFSET " + start + " ROWS FETCH FIRST " + size + " ROW ONLY";
	final List<Integer> rest = new ArrayList<Integer>();
	try (DataSource ds = new DataSourceBuilder()
	.url(url)
	.username(user)
	.password(password)
	.build();
	Session conn = ds.getSession(t -> System.out.println("ERROR: " + t.getMessage()))) {
	CompletionStage<List<Integer>> data = conn.<List<Integer>>rowOperation(SQL)
	.collect(Collector.of(
	() -> rest,
	(a, r) -> {
	r.at(0).get().toString();
	r.at(1).get();
	},
	(l, r) -> null,
	a -> a)
	)
	.submit()
	.getCompletionStage();
	}
	ForkJoinPool.commonPool().awaitQuiescence(1, TimeUnit.MINUTES);
	return rest;
	}

view raw NonBlockingJDBCRunner.java hosted with ❤ by GitHub

Update:

	try (DataSource ds = new DataSourceBuilder()
	.url(url)
	.username(user)
	.password(password)
	.build();
	Session conn = ds.getSession(t -> System.out.println("ERROR: " + t.getMessage()))) {
	for (int i = startIndex; i < startIndex + size; i++) {
	String SQL = "insert into testdata values (" + i + ", '" + String.valueOf(i) + "')";
	conn.<Long>rowCountOperation(SQL)
	.apply(c -> c.getCount())
	.onError(t -> t.printStackTrace())
	.submit();
	}
	conn.catchErrors();
	}
	// wait for the async tasks to complete before exiting
	ForkJoinPool.commonPool().awaitQuiescence(1, TimeUnit.MINUTES);

view raw NonBlockingJDBCRunner.java hosted with ❤ by GitHub

See my github for full implementation:

https://github.com/LeiZheng/sqlperformance