How to reduce the gap between Diesel's sql_query (RAW) and the ORM mode ?

[rxdiscovery]

Hello,

For high-performance project requirements, I decided to do my own performance test between SqlX and Async-Diesel (RAW + ORM) to make my choice.
(I've made sure that the code is as faithful as possible between the two, without any excess)

I'm mainly interested in Diesel, since it offers a hybrid approach. but when I want to use native queries, it lacks performance...

---

I sent 20,000 queries to a Postgres database with 6 test types :

• Sqlx-NS ( sqlx::query_as )
• Sqlx-NT ( transaction + sqlx::query_as )
• Diesel-NS ( diesel::sql_query )
• Diesel-NT ( transaction + diesel::sql_query )
• Diesel-OS ( as_select() )
• Diesel-OT ( transaction + as_select() )

S = Simple ( without transaction )
T = Transaction
N = Native query
O = ORM mode

---

Pools config :
Sqlx :

    let pool = PgPoolOptions::new()
        .max_connections(200)
        .acquire_timeout(std::time::Duration::from_secs(10))
        .connect_with(options)
        .await
        .expect("Pool build error");

Diesel :

    let pool = diesel_async::pooled_connection::deadpool::Pool::builder(manager)
        .max_size(200)
        .build()
        .expect("Pool build error");

---

the crates :

chrono = { version = "0.4.38", features = ["serde"] }
crossterm = "0.27.0"
diesel = { version = "2.1.6",features = ["chrono"]}
diesel-async = { version = "0.4.1", features = ["postgres", "deadpool"] }
ratatui = "0.26.3"
serde = { version = "1.0.203", features = ["derive"] }
sqlx = { version = "0.7.4", features = ["runtime-tokio", "postgres", "time", "chrono"] }
tokio = { version = "1.37.0", features = ["full"] }

---

Build options :

[profile.release]
strip = true
panic = "abort"
lto = true
opt-level = 3
debug = false
codegen-units = 1

[ I'll create a repo on git and post the complete project later. ] 🥸

Here are the results for 20'000 iterations (ms) :

Sqlx-NS	Sqlx-NT	Diesel-NS	Diesel-NT	Diesel-OS	Diesel-OT
2518	4474	3894	5688	2045	4291

---

what I can conclude is that Diesel in pure ORM mode is slightly faster than Sqlx, but when you use its query builder it's ~50% times slower than Sqlx.

Simple      > sqlx vs async-diesel (ORM mode) = Diesel win : -18%
Transaction > sqlx vs async-diesel (ORM mode) = Diesel win : -4%

Simple      > sqlx vs async-diesel (RAW mode) = Sqlx win   : -54%
Transaction > sqlx vs async-diesel (RAW mode) = Sqlx win   : -27%

Diesel in ORM mode deserialize data by index, whereas in Raw mode it uses names, I think that's what's causing the performance problem?

is there a way in Diesel to make the sql_query more efficient ?

Since Diesel outperforms sqlx in generating queries via functions, then normally if it executes RAW queries, it must be even faster?

Thank you in advance for your help..

[weiznich]

I fear it's hard to answer this specific question without knowing your exact benchmark code and setup. That said it is currently expected that sql_query is somewhat slower than using the dsl (as shown by our own benchmarks). That's because the DSL is able to utilise compile time type information about your query that are not available for sql_query. There these information need to be computed at runtime which requires additional time.

That all written: We are always open to accept contributions that improve performance as long as they do not break existing code.

[rxdiscovery]

Thank you very much for your reply, I'll dive into the code to see if I can help optimize this part.

I'll soon be posting the Benchmark code on github, I'm in the process of enriching it with lots of other crates, to make it even more useful. (in sequential and parallel mode)

[belovaf]

@rxdiscovery I'm in the process of choosing ORM for high-performance project too.
I benchmarked the diesel and I think that I found a bottleneck.
For any query which contains at least one raw sql fragment (sql or sql_query) diesel unconditionally disables prepared statement cache.
I think that developer should be able to forcefully enable it, but diesel doesn't provide such api.
I implemented my own sql_prepared_query function and it works much faster.

@weiznich what are your thoughts about this problem? I can make a PR with this function, but there are a lot of duplicated logic. I think it would be better to extend existing functions with explicit is_safe_to_cache flag.

[rxdiscovery]

@belovaf it's very interesting what you've discovered, do you have a percentage gain with the cache activated and without? or is it insignificant?

[belovaf]

I end up creating new type and implementing all required traits for each custom query.
This way it works exactly as fast as any other ORM query (almost x2 faster).

[belovaf]

If you share your repository with benchmarks I can add a reworked sql_query example.

[rxdiscovery]

ok, I'll upload the code and share the repo