This pull request globally improves the performance of the program by removing most of the allocations at run time. Basically, the changes are in the lib.rs file.

• I changed the common.rs module to be lib.rs file, this way cargo will not try to compile it like a binary file.
• I removed the Vec creation in the get_random_age function.
• I replaced the format! calls with an AreaCode new type that wraps a TinyStr8. By using this instead of strings. It helps kipping memory locality by directly storing the formatted string on a pointer-sized integer (64 bytes) without any indirection to read the str itself and also removes any possible allocation.

On my side, the performance gain is around 17.6%.

fastrand should be a lot better suited, less hassle with threads trying to optimize random selections. did not test the threaded build tbh but should be ok.

My 10M (sqlite3_opt_batched) run goes from 42s to 26s.

Switching to https://docs.python.org/3/library/os.html#os.urandom would make this even faster, but I don't think that's the point of the exercise so leaving it at this. I think it's still nice to have because it reduces the time by a lot and makes it faster for me to benchmark

result for threaded_batched.py against pypy3 on my i7 for all 100M records:

real 111.251 user 104.187 sys 4.483 pcpu 97.68

I seem to be running out of memory though (process keeps getting OOM-killed, wondering if that can be fixed somehow).

/usr/bin/time doesn't exist on many systems because it is implemented as a shell-builtin.

Putting a semi-colon at the end of the command forces make to run a shell for the same, and invokes the correct time.

Added a shebang for bench.sh so hopefully that should also work everywhere?

Ref: https://stackoverflow.com/a/17550243/368328

Based from #11. This PR adds a new method of quickly inserting 100M rows into a SQLite table, using a pure SQLite approach.

Using the generate_series table valued function, we can insert 100M rows into a table very, very quickly with a single INSERT INTO ... SELECT statement.

The question of "how fast is this" is difficult to answer. I'm not sure if it's something with my computer/background apps I'm running, but my benchmarks of the previous python/rust solutions have been all over the place. Using this approach, I get 57s. For the pypy threaded_batched approach, I get 5m50s, and with the rust threaded_batched approach, I get 1m9s.

$ time sqlite3x sqlite3.db  '.read load.sql'
real 0m57.414s
user 0m45.714s
sys 0m6.574s

$ time ~/Downloads/pypy3.8-v7.3.7-osx64/bin/pypy3 threaded_batched.py
real 5m49.887s
user 3m4.141s
sys 1m52.690s

$ time ./target/release/threaded_batched
real 1m9.807s
user 0m49.430s
sys 0m29.562s

Given this, I'm tempted to say that this actually might be the fastest solution, but given my wildly different benchmark times (pypy's 5m50s vs 126s, rust's 1m9s vs 30s), I'd love to see how this runs on your computer, using the full benchmark!

Why (I think) it's so fast

One solid reason why I think this approach is so fast is because it's only a single SQL statement that is being ran. All the other approaches are performing millions of insert into ... values (...) SQL statements at a time, which is a fast operation, but doing anything millions of times starts to add up. We see an obvious benefit when we start to batch statements together, but even batching 50 at a time is still 2 million distinct SQL statements that need to be ran.

Also, by using generate_series, all operations are kept in C, which is very fast. Rust and pypy are also fast, but my guess is that context switching between Rust -> C or pypy -> C takes a non-trivial amount of time that adds up fast.

Also, on generate_series vs recursive CTEs, the generate_series page mentions that recursive CTEs are slower than table-valued functions.

Note: generate_series may not be in your sqlite CLI by default, so make sure the sqlite3 CLI that you have contains that

Would love to hear what you think, and to see the "official" benchmark numbers!

I might be missing something here as I'm still new to async in rust, but as I understand it calling await on a future forces the runtime to serialize the execution of the program, this means that this code:

conn.execute("PRAGMA cache_size = 1000000;").await?;
conn.execute("PRAGMA locking_mode = EXCLUSIVE;").await?;
conn.execute("PRAGMA temp_store = MEMORY;").await?;

is completely synchronized and each call to the database needs to complete before the next call starts. Now, this seems fine as this only happens once outside of the benchmark loop.

But all the code in the faker function also uses await on each async call. For example this code :

let stmt_with_area = tx
    .prepare("INSERT INTO user VALUES (NULL, ?, ?, ?)")
    .await?;
let stmt = tx
    .prepare("INSERT INTO user VALUES (NULL, NULL, ?, ?)")
    .await?;

It seems to me that there is no reason the needs to be serialized and we should join these calls, in something like:

let (stmt_with_area, stmt ) = tokio::join!(
    tx.prepare("INSERT INTO user VALUES (NULL, ?, ?, ?)"),
    tx.prepare("INSERT INTO user VALUES (NULL, NULL, ?, ?)")
)

Finally, it also seems that we don't actually need to await the execution of the statements themselves inside the for but that would require saving all the futures in a big vector and then joining them, which seems a bit odd and would consume a lot of memory when running the for loop for 10e6 iterations.

As described in the other PR #12 I discovered the "slow" Display::fmt being called for formatting the area code using perf.

This PR does limit the generated area code to numbers above 100_000 where before it could be less, thus losing some randomness, but it's about the same "trick" as used in #2 for the python side.

This PR enables binaries who uses rusqlite to statically linked with libsqlite3.a compiled with LTO using linker-plugin-lto.

To compile these binaries (excluding basic_async.rs), just run make -j $(nproc). It will compile sqlite3.c using CFLAGS='-O2 -flto'. The generated binaries will be smaller, ~~though I haven't tested the performance yet, I will add the benchmark below as a comment~~ ~~but only provides minor performance improvements (see comments below)~~ It seems that I didn't enable LTO in rust (see comments below).

To compile basic_async.rs, run cargo build --release --bin basic_async --features async-sql.

This PR might be related to #14

Signed-off-by: Jiahao XU [email protected]