Benchmarking batch JDBC queries

What happened?

case class Entity(id: String, val1: String, val2: Boolean, val3: Array[Byte]) def add(entity: Entry): Unit = autoCommit { implicit session =>
sql"""
INSERT INTO tbl (id, col1, col2, col3)
VALUES (${entity.id}, ${entity.val1}, ${entity.val2}, ${entity.val3})
"""
.update()
.apply()
}
def remove(id: String): Unit = autoCommit { implicit session =>
sql"""DELETE FROM tbl WHERE id = $id"""
.update()
.apply()
}

Batching

INSERT INTO tbl (...) VALUES(...);
INSERT INTO tbl (...) VALUES(...);
def add(entities: Seq[Entity]): Unit = localTx { implicit session =>
val params = entities.map { entity =>
Seq(
"id" -> entity.id,
"val1" -> entity.val1,
"val2" -> entity.val2,
"val3" -> entity.val3,
)
}
sql"""
INSERT INTO tbl (id, col1, col2, col3)
VALUES ({id}, {val1}, {val2}, {val3})
"""
.batchByName(params: _*)
.apply()
}
def remove(ids: Seq[String]): Unit = localTx { implicit session =>
val params = ids.map(v => Seq(v))
sql"""DELETE FROM tbl WHERE id = ?"""
.batch(params: _*)
.apply()
}

Better DELETE (IN clause)

def remove(ids: Seq[String]): Unit = autoCommit { implicit session =>
val inClause = SQLSyntax.csv(ids.map(id => sqls"""$id"""): _*)
sql"""DELETE FROM tbl WHERE id IN ($inClause)"""
.update()
.apply()
}

rewriteBatchedStatements

INSERT INTO tbl (...)
VALUES (...), (...), (...)

Benchmarks

INSERT

Benchmark    (numberOfEntities)  Mode  Cnt     Score   Error  Unitsbatch                         1  avgt    2    12.797          ms/op
batchRewriting 1 avgt 2 11.769 ms/op
oneByOneInTransaction 1 avgt 2 12.624 ms/op
oneByOne 1 avgt 2 12.184 ms/op
batch 10 avgt 2 13.433 ms/op
batchRewriting 10 avgt 2 11.835 ms/op
oneByOneInTransaction 10 avgt 2 15.592 ms/op
oneByOne 10 avgt 2 125.161 ms/op
batch 100 avgt 2 29.763 ms/op
batchRewriting 100 avgt 2 22.480 ms/op
oneByOneInTransaction 100 avgt 2 35.664 ms/op
oneByOne 100 avgt 2 1281.417 ms/op
batch 1000 avgt 2 213.938 ms/op
batchRewriting 1000 avgt 2 148.009 ms/op
oneByOneInTransaction 1000 avgt 2 229.646 ms/op
batch 10000 avgt 2 2027.138 ms/op
batchRewriting 10000 avgt 2 1497.429 ms/op
oneByOneInTransaction 10000 avgt 2 2321.587 ms/op

DELETE

Benchmark  (numberOfEntities)  Mode  Cnt     Score   Error  Units
batch 1 avgt 2 21.636 ms/op
batchRewriting 1 avgt 2 15.237 ms/op
inClause 1 avgt 2 13.483 ms/op
oneByOneInTransaction 1 avgt 2 10.938 ms/op
oneByOne 1 avgt 2 12.273 ms/op
batch 10 avgt 2 16.328 ms/op
batchRewriting 10 avgt 2 14.396 ms/op
inClause 10 avgt 2 11.184 ms/op
oneByOneInTransaction 10 avgt 2 13.085 ms/op
oneByOne 10 avgt 2 124.575 ms/op
batch 100 avgt 2 21.893 ms/op
batchRewriting 100 avgt 2 17.696 ms/op
inClause 100 avgt 2 13.029 ms/op
oneByOneInTransaction 100 avgt 2 24.492 ms/op
oneByOne 100 avgt 2 1181.656 ms/op
batch 1000 avgt 2 104.244 ms/op
batchRewriting 1000 avgt 2 83.070 ms/op
inClause 1000 avgt 2 25.444 ms/op
oneByOneInTransaction 1000 avgt 2 130.383 ms/op
batch 10000 avgt 2 925.338 ms/op
batchRewriting 10000 avgt 2 854.990 ms/op
inClause 10000 avgt 2 167.237 ms/op
oneByOneInTransaction 10000 avgt 2 1254.676 ms/op

Conclusion

--

--

--

Software developer, moved to Israel from Russia, trying to be aware of things.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Data Driven Framework

How to run Quest 2 without Facebook — Experimenting with Test Account

Deploy my web app to BigCommerce / Square / Wix / Shopify

Workers and Node: kue it up.

Why You Should Use Snowflake’s External Tables

Bored of Imperative For Loops? Use Functional Operators Instead.

A child organizing candies by color.

API Gateway, Lambda Proxy, Swagger, & API Key

SQLite in Futter, simple implementation

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Dmitry Komanov

Dmitry Komanov

Software developer, moved to Israel from Russia, trying to be aware of things.

More from Medium

Reactive Web Application: With Play, Akka, and Reactive Streams — Part 1

sbt Library Dependencies et al

Introduction of MongoDB Scala Driver

MongoDB Database and Collection with Custom Type

Sending email through scala using JAVA mail API ( For Gmail )