Optimize SQLite range reads with substr()

[jerome-caucat]

Hello everyone,

Currently, when reading data using the SQLite service (and other KV adapter-based services), the entire buffer is fetched before slicing. See read.

We could leverage the SQLite substr() function and only read the requested range. This optimization would significantly improve performance when reading slices of large data.

I propose submitting a PR with the following changes:

• A new kv::Adapter method :

fn get_range(&self, path: &str, offset: u64, length: Option<u64>) -> impl Future<Output = Result<Option<Buffer>>> + MaybeSend

• That can then be implemented in the SQLite service like so:

async fn get_range(&self, path: &str, offset: u64, length: Option<u64>) -> Result<Option<Buffer>> {
    let pool = self.get_client().await?;

     let substr_query = match length {
        Some(len) => format!("SUBSTR(`{}`, {}, {})", self.value_field, offset + 1, len),
        None => format!("SUBSTR(`{}`, {})", self.value_field, offset + 1),
    };
    let value: Option<Vec<u8>> = sqlx::query_scalar(&format!(
        "SELECT {} FROM `{}` WHERE `{}` = $1 LIMIT 1",
        substr_query, self.table, self.key_field
    ))
    .bind(path)
    .fetch_optional(pool)
    .await
    .map_err(parse_sqlite_error)?;

    Ok(value.map(Buffer::from))
}

• And called instead of get when available.

Other services with similar feature could then also be improved.

What do you think?

[Xuanwo]

I think that's a good idea. In fact, I'm considering passing OpRead directly in kv::Access so that services can handle it more effectively.

[jerome-caucat]

@Xuanwo Could you please elaborate? I’d be happy to implement the solution, but I need a bit more guidance.

[Xuanwo]

I started a discussion at #5703

I think we can just implement Access instead of kv::Adapter for sqlite.