SimpleTable benchmarks

bench.scala

//> using jmh
//> using toolkit 0.7.0
//> using scala 3.7.0
//> using dep org.scala-lang::scala3-compiler:3.7.0

package bench

import dotty.tools.dotc.Driver
import org.openjdk.jmh.annotations.State
import org.openjdk.jmh.annotations.Scope
import dotty.tools.io.PlainFile
import dotty.tools.io.Path
import org.openjdk.jmh.annotations.BenchmarkMode
import org.openjdk.jmh.annotations.OutputTimeUnit
import org.openjdk.jmh.annotations.Mode
import java.util.concurrent.TimeUnit
import org.openjdk.jmh.annotations.Warmup
import org.openjdk.jmh.annotations.Measurement
import org.openjdk.jmh.annotations.Fork
import org.openjdk.jmh.annotations.Benchmark
import dotty.tools.io.AbstractFile

trait SetupClasspath(val classpath: String, files: Map[String, Seq[os.Path]]):
  // val dir = os.pwd.toString

  val sources: Map[String, List[AbstractFile]] =
    files.map(
      (n, fs) =>
        n -> fs.zipWithIndex.map((f, i) => PlainFile.apply(Path(f.toNIO))).toList
    )

  object Compiler extends Driver:
    override def sourcesRequired: Boolean = false
    def run(key: String): Unit =
      val ictx = initCtx
      setup(Array("--classpath", classpath, "-Ystop-after:erasure"), ictx).map(_(1)).getOrElse(ictx) match
        case given dotty.tools.dotc.core.Contexts.Context =>
          val cmp = newCompiler
          doCompile(cmp, sources(key))

object Benchmarks {

  @State(Scope.Benchmark)
  class ConcreteSetup extends SetupClasspath(
    sys.env("SCALASQL_CLASSPATH") // output of $(cs fetch -p com.lihaoyi:scalasql-namedtuples_3:0.1.20),
    Map(
      "scalasql-table" -> Seq(os.pwd / "scalasql.scala"),
      "scalasql-simpletable" -> Seq(os.pwd / "scalasql-simpletable.scala")
    )
  )
}

@BenchmarkMode(Array(Mode.AverageTime))
@OutputTimeUnit(TimeUnit.NANOSECONDS)
@Warmup(iterations = 25, time = 100, timeUnit = TimeUnit.MILLISECONDS)
@Measurement(iterations = 50, time = 100, timeUnit = TimeUnit.MILLISECONDS)
@Fork(0)
class Benchmarks {
  @Benchmark
  def oldTable(state: Benchmarks.ConcreteSetup): Unit = {
    state.Compiler.run("scalasql-table")
  }
  @Benchmark
  def newSimpleTable(state: Benchmarks.ConcreteSetup): Unit = {
    state.Compiler.run("scalasql-simpletable")
  }
}

scalasql-simpletable.scala

import scalasql.simple.*
import scalasql.H2Dialect.*
import java.time.LocalDate
import java.time.LocalTime
import java.time.LocalDateTime
import java.text.SimpleDateFormat
import java.util.Date
import java.time.Instant

case class Nested(
    fooId: Int,
    myBoolean: Boolean
) extends SimpleTable.Nested
object Nested extends SimpleTable[Nested]

case class Enclosing(
    barId: Int,
    myString: String,
    foo: Nested
)
object Enclosing extends SimpleTable[Enclosing]

extension [T](t: T) def ==> [U](u: U): Unit = assert(t == u, s"Expected $u but got $t")

def foo(db: DbApi): Unit = {
  object MyEnum extends Enumeration {
    val foo, bar, baz = Value

    implicit def make: String => Value = withName
  }
  case class DataTypes(
      myTinyInt: Byte,
      mySmallInt: Short,
      myInt: Int,
      myBigInt: Long,
      myDouble: Double,
      myBoolean: Boolean,
      myLocalDate: LocalDate,
      myLocalTime: LocalTime,
      myLocalDateTime: LocalDateTime,
      myUtilDate: Date,
      myInstant: Instant,
      myVarBinary: geny.Bytes,
      myUUID: java.util.UUID,
      myEnum: MyEnum.Value
  )

  object DataTypes extends SimpleTable[DataTypes]

  val value = DataTypes(
    myTinyInt = 123.toByte,
    mySmallInt = 12345.toShort,
    myInt = 12345678,
    myBigInt = 12345678901L,
    myDouble = 3.14,
    myBoolean = true,
    myLocalDate = LocalDate.parse("2023-12-20"),
    myLocalTime = LocalTime.parse("10:15:30"),
    myLocalDateTime = LocalDateTime.parse("2011-12-03T10:15:30"),
    myUtilDate =
      new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS").parse("2011-12-03T10:15:30.000"),
    myInstant = Instant.parse("2011-12-03T10:15:30Z"),
    myVarBinary = new geny.Bytes(Array[Byte](1, 2, 3, 4, 5, 6, 7, 8)),
    myUUID = new java.util.UUID(1234567890L, 9876543210L),
    myEnum = MyEnum.bar
  )

  db.run(
    DataTypes.insert.columns(
      _.myTinyInt := value.myTinyInt,
      _.mySmallInt := value.mySmallInt,
      _.myInt := value.myInt,
      _.myBigInt := value.myBigInt,
      _.myDouble := value.myDouble,
      _.myBoolean := value.myBoolean,
      _.myLocalDate := value.myLocalDate,
      _.myLocalTime := value.myLocalTime,
      _.myLocalDateTime := value.myLocalDateTime,
      _.myUtilDate := value.myUtilDate,
      _.myInstant := value.myInstant,
      _.myVarBinary := value.myVarBinary,
      _.myUUID := value.myUUID,
      _.myEnum := value.myEnum
    )
  ) ==> 1

  db.run(DataTypes.select) ==> Seq(value)

}

def bar(db: DbApi): Unit = {
  val value1 = Enclosing(
    barId = 1337,
    myString = "hello",
    foo = Nested(
      fooId = 271828,
      myBoolean = true
    )
  )
  val value2 = Enclosing(
    barId = 31337,
    myString = "world",
    foo = Nested(
      fooId = 1618,
      myBoolean = false
    )
  )

  val insertColumns = Enclosing.insert.columns(
    _.barId := value1.barId,
    _.myString := value1.myString,
    _.foo.fooId := value1.foo.fooId,
    _.foo.myBoolean := value1.foo.myBoolean
  )
  db.renderSql(insertColumns) ==>
    "INSERT INTO enclosing (bar_id, my_string, foo_id, my_boolean) VALUES (?, ?, ?, ?)"

  db.run(insertColumns) ==> 1

  val insertValues = Enclosing.insert.values(value2)
  db.renderSql(insertValues) ==>
    "INSERT INTO enclosing (bar_id, my_string, foo_id, my_boolean) VALUES (?, ?, ?, ?)"

  db.run(insertValues) ==> 1

  db.renderSql(Enclosing.select) ==> """
    SELECT
      enclosing0.bar_id AS bar_id,
      enclosing0.my_string AS my_string,
      enclosing0.foo_id AS foo_id,
      enclosing0.my_boolean AS my_boolean
    FROM enclosing enclosing0
  """

  db.run(Enclosing.select) ==> Seq(value1, value2)
}

scalasql.scala

import scalasql.*
import scalasql.H2Dialect.*
import java.time.LocalDate
import java.time.LocalTime
import java.time.LocalDateTime
import java.text.SimpleDateFormat
import java.util.Date
import java.time.Instant

case class Nested[T[_]](
    fooId: T[Int],
    myBoolean: T[Boolean]
)
object Nested extends Table[Nested]

case class Enclosing[T[_]](
    barId: T[Int],
    myString: T[String],
    foo: Nested[T]
)
object Enclosing extends Table[Enclosing]

extension [T](t: T) def ==> [U](u: U): Unit = assert(t == u, s"Expected $u but got $t")

def foo(db: DbApi): Unit = {
  object MyEnum extends Enumeration {
    val foo, bar, baz = Value

    implicit def make: String => Value = withName
  }
  case class DataTypes[T[_]](
      myTinyInt: T[Byte],
      mySmallInt: T[Short],
      myInt: T[Int],
      myBigInt: T[Long],
      myDouble: T[Double],
      myBoolean: T[Boolean],
      myLocalDate: T[LocalDate],
      myLocalTime: T[LocalTime],
      myLocalDateTime: T[LocalDateTime],
      myUtilDate: T[Date],
      myInstant: T[Instant],
      myVarBinary: T[geny.Bytes],
      myUUID: T[java.util.UUID],
      myEnum: T[MyEnum.Value]
  )

  object DataTypes extends Table[DataTypes]

  val value = DataTypes[Sc](
    myTinyInt = 123.toByte,
    mySmallInt = 12345.toShort,
    myInt = 12345678,
    myBigInt = 12345678901L,
    myDouble = 3.14,
    myBoolean = true,
    myLocalDate = LocalDate.parse("2023-12-20"),
    myLocalTime = LocalTime.parse("10:15:30"),
    myLocalDateTime = LocalDateTime.parse("2011-12-03T10:15:30"),
    myUtilDate =
      new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS").parse("2011-12-03T10:15:30.000"),
    myInstant = Instant.parse("2011-12-03T10:15:30Z"),
    myVarBinary = new geny.Bytes(Array[Byte](1, 2, 3, 4, 5, 6, 7, 8)),
    myUUID = new java.util.UUID(1234567890L, 9876543210L),
    myEnum = MyEnum.bar
  )

  db.run(
    DataTypes.insert.columns(
      _.myTinyInt := value.myTinyInt,
      _.mySmallInt := value.mySmallInt,
      _.myInt := value.myInt,
      _.myBigInt := value.myBigInt,
      _.myDouble := value.myDouble,
      _.myBoolean := value.myBoolean,
      _.myLocalDate := value.myLocalDate,
      _.myLocalTime := value.myLocalTime,
      _.myLocalDateTime := value.myLocalDateTime,
      _.myUtilDate := value.myUtilDate,
      _.myInstant := value.myInstant,
      _.myVarBinary := value.myVarBinary,
      _.myUUID := value.myUUID,
      _.myEnum := value.myEnum
    )
  ) ==> 1

  db.run(DataTypes.select) ==> Seq(value)

}

def bar(db: DbApi): Unit = {
  val value1 = Enclosing[Sc](
    barId = 1337,
    myString = "hello",
    foo = Nested[Sc](
      fooId = 271828,
      myBoolean = true
    )
  )
  val value2 = Enclosing[Sc](
    barId = 31337,
    myString = "world",
    foo = Nested[Sc](
      fooId = 1618,
      myBoolean = false
    )
  )

  val insertColumns = Enclosing.insert.columns(
    _.barId := value1.barId,
    _.myString := value1.myString,
    _.foo.fooId := value1.foo.fooId,
    _.foo.myBoolean := value1.foo.myBoolean
  )
  db.renderSql(insertColumns) ==>
    "INSERT INTO enclosing (bar_id, my_string, foo_id, my_boolean) VALUES (?, ?, ?, ?)"

  db.run(insertColumns) ==> 1

  val insertValues = Enclosing.insert.values(value2)
  db.renderSql(insertValues) ==>
    "INSERT INTO enclosing (bar_id, my_string, foo_id, my_boolean) VALUES (?, ?, ?, ?)"

  db.run(insertValues) ==> 1

  db.renderSql(Enclosing.select) ==> """
    SELECT
      enclosing0.bar_id AS bar_id,
      enclosing0.my_string AS my_string,
      enclosing0.foo_id AS foo_id,
      enclosing0.my_boolean AS my_boolean
    FROM enclosing enclosing0
  """

  db.run(Enclosing.select) ==> Seq(value1, value2)
}

seems currently a 1.5x slowdown using SimpleTable

Benchmark                  Mode  Cnt          Score         Error  Units
Benchmarks.newSimpleTable  avgt   50  296821159.080 ± 9669713.596  ns/op
Benchmarks.oldTable        avgt   50  187522719.060 ± 2617395.670  ns/op