SQLite insert performance

[nhenezi]

I'm trying to get sea_query to be as performant as native SQLite prepared statement as possible. I've only tested TEXT columns and here's the fastest I've managed to do it:

    let mut q = sea_query::Query::insert()
        .into_table(S::S("test_table".to_string()))
        .columns(vec![
            S::S("col1".to_string()),
            S::S("col2".to_string()),
            S::S("col3".to_string()),
            S::S("col4".to_string()),
            S::S("col5".to_string()),
            S::S("col6".to_string()),
            S::S("col7".to_string()),
            S::S("col8".to_string()),
            S::S("col9".to_string()),
            S::S("col10".to_string()),

        ])
        .to_owned();
    q.values_panic(data[0].clone());
    let (sql, _) = q.build(SqliteQueryBuilder);
    let mut prepared_stmt = conn.prepare(&sql).unwrap();
    let start = Instant::now();
    conn.execute("BEGIN TRANSACTION;", []).unwrap();
    for d in data {
        let rq_vals = RusqliteValues::from(Values(d));
        prepared_stmt.execute(rq_vals.as_params().as_slice()).unwrap();

    }
    conn.execute("COMMIT;", []).unwrap();

by using the fact that I know that all values have the same structure so I use only the first one to construct a prepared statement that is utilized during the insertion - that seemed to produce the equivalent to the fastest SQLite insertion and consequentially fastest sea_query insertions:

Using SQLite prepared statement
1000000 entries inserted in ~1.149 sec. ~870000 inserts/sec
Using sea_query with guard and prepared statement
1000000 entries inserted in ~1.541 sec. ~648000 inserts/sec

It's still 25% slower which I would like to bring down to 5-10% if possible. A few different approaches and a more extensive set of experiments you can find at https://gitlab.com/nhenezi/rust-sea-query-perf-test/-/blob/master/src/main.rs .

I'm suspecting that the .as_params() is the bottleneck, as results gets worse the more columns I add to the performance test and .as_params() has to iterate over all values and convert them to something that SQLite understands.

I see two approaches that I can take here, but neither one is doable in my case:

• parallelization approach to serializing to SQLite where I wouldn't use .as_params() but rather in a parallelized manner convert every Value individually to its SQLite representation
• macro based approach where iteration is shifted from runtime to compile time

Do you see any other way to increase performance here? Am I missing something that could shave off some time off?

[billy1624]

Hey @nhenezi, thanks for digging so deep into it!

I'm suspecting that the .as_params() is the bottleneck, as results gets worse the more columns I add to the performance test and .as_params() has to iterate over all values and convert them to something that SQLite understands.

You are probably right. The conversion comes with overhead. Also, I suspect wrapper struct of RusqliteValues & RusqliteValue could cause overhead as well.

Btw... are you building something that requires high performance?

[nhenezi]

For future visitors to this issue - approach I took was that sea_query is used to define all metainformation (table, columns, constraints, etc.). We use sea_query::Query to construct the actual query, but supply only one row to .valus_panic so that we have a properly formed statement. We can then build that using SqliteQueryBuilder to get a prepared statement that can be used normally with rusqlite and plug in existing rusqlite::Values into that.

That removes overhead of using sea_query::Value, but provides a drawback that you cannot so easily swap out a different db., but achieves performances that are comparable to rusqlite; e.g. raw stringlike SQL. For our use case, that's an acceptable tradeoff due to how nicer table definitions look like and the ability to use query builders. There shouldn't be any problems on the retrieval side as conversions like this shouldn't be a bottleneck.

---

Yeah, high performance is a requirement. We have built a system that achieves that using raw SQLite, because at the time of writing there was nothing that could support the dynamic nature of data that we have to deal with. sea_query is exactly what we have been looking for and are rewriting now in sea_query to get rid of raw SQL. Quite enjoyable experience so far and hopefully soon I'll have some time to tackle features we are missing from sea_query (views, FTS). Thank you for tackling dynamic query building in rust!

[billy1624]

Thanks for the adoption! Looking forward to your PR hahaa :)

[tyt2y3]

Just a sanity check, are you using Rusqlite and testing with --release?

[tyt2y3]

There is some basic benchmark in https://github.com/SeaQL/sea-query/tree/master/benches may be you can add more benchmarks and use that to dissect the overhead. Although if you wanted to have zero runtime overhead, you should consider using sea-query at compile-time (inside your own macro crate) and split out a static string and have it only prepared once.

[tyt2y3]

I strongly believe that the as_params is a pure type system exercise that LLVM would be able to optimize away. If that's not the case, then we could probably use ParamsFromIter so that the Vec allocation does not actually occur