libsql rust slow compare to rusqlite in local-mode

[ignatz]

Hey folks,

I love the idea behind libsql and spent a little time trying it out.

It felt a bit slow so I compared it to a popular rust alterative for sqlite. I found libsql to be 200+ times slower. I could totally understand some small overhead in networked mode to heavily skew the results for a silly sequential-insert micro-benchmark but for local mode it does feel a bit tall.

Anyway, I documented my findings (+some minor surprises regarding the API) and reproducible benchmarks here. In the best case, I'm just holding it wrong. Either way, I hope it provides some insights.

Thanks :pray:

[sivukhin]

@ignatz, from what I see in the libsql code conn.query(PRAGMAS, ()).await.unwrap(); will execute only first query in the multi-statement SQL...

So, actually you compare rusqlite in WAL mode with libsql in DELETE mode (which perform a lot of I/O I guess - that what you observed actually).

But I guess the API indeed misleading: maybe it's better to fail with multi-statement queries in the query(...) method and also provide query_batch(...) which will return some form of iterator with Result<Rows> entries... (and actually issue about that already here: https://github.com/tursodatabase/libsql/issues/1340)

[ignatz]

@sivukhin awesome and thanks! Happy to just holding it wrong :) - I'm happy to report that libsql is maybe even a tiny smidge faster :tada:

But I guess the API indeed misleading: maybe it's better to fail with multi-statement queries in the query(...) method and also provide query_batch(...) which will return some form of iterator with Result entries... (and actually issue about that already here: https://github.com/tursodatabase/libsql/issues/1340)

Isn't that specifically what execute_batch is for? I did take a closer look at what the rows are that it wants to return. Setting the following constants:

    PRAGMA busy_timeout       = 10000;
    PRAGMA journal_mode       = WAL;
    PRAGMA journal_size_limit = 200000000;
    PRAGMA synchronous        = NORMAL;
    PRAGMA foreign_keys       = ON;
    PRAGMA temp_store         = MEMORY;
    PRAGMA cache_size         = -16000;

returns:

Row: {Some("timeout"): (Integer, 10000)}
Row: {Some("journal_mode"): (Text, "wal")}
Row: {Some("journal_size_limit"): (Integer, 200000000)}

While it does look a bit inconsistent, it's actually the same that rusqlite returns. I guess the only difference is that rusqlite doesn't mind if execute returns a row :woman_shrugging:

Just curious and off topic, is there any timeline on extensions support?

Awesome, thanks again :pray:

[ignatz]

Just curious and off topic, is there any timeline on extensions support?

I guess the timeline is "now". Thanks folks!

I leave it up to you folks if execute(_btched) should ignore any returned rows. Thanks again