zio/zio-sql

add the ability to call methods on the underlying JDBC `Statement` eg `setFetchSize`

Opened this issue · 0 comments

The automatic behaviour for drivers such as MySql is to read all the records of the resultset into memory. For very large resultsets this is undesirable and will blow memory client side. One way to control this is to manipulate the jdbc Statement object see this SO post
Eg statement.fetchSize = Integer.MIN_VALUE will give a hint to the driver to emit one row at a time.

So you would end up with code that is equivalent to this hard coded example

    override def readOn[A](read: Read[A], conn: Connection): Stream[Exception, A] =
      Stream.unwrap {
        blocking.effectBlocking {
          val schema = getColumns(read).zipWithIndex.map { case (value, index) =>
            (value, index + 1)
          } // SQL is 1-based indexing

          val query = renderRead(read)

          val statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)
          statement.setFetchDirection(ResultSet.FETCH_FORWARD)
          statement.setFetchSize(Int.MinValue)
     // etc etc   

I would normally pick this up but I am a bit snowed under ATM.

Actually as readOn returns a ZStream maybe we can get away with just hard coding the above for now for a quick win? - I can certainly create a PR for this simple defaulting.