Update SQLAlchemy demo to use modern 1.4 / 2.0 idioms and add asyncio benchmark

[zzzeek]

Hi -

I've seen this repo coming up in tweets lately, took a look and thought I would modernize it with our latest APIs. I appreciate that this benchmark suite took the time to make use of the "baked" API in order to give SQLAlchemy the best performance boost possible; the good news is 90% of the performance increase of "baked" is now standard for all SQLAlchemy queries.

As SQLAlchemy's place in the benching among Python systems is firmly in the spot "a little above Django and well below EdgeDB", while this PR does include some performance enhancements, I don't expect much in the way of speed improvements, although I was able to reduce the number of queries for a few of the test functions which should help. What's important though is that as this benching suite is serving as one of relatively few examples of comparing and contrasting different object-relational applications, that the API use illustrated is also informative in itself, showing off just what it looks like to be using the tools in their most idiomatic way in the current day. So from that perspective I am enthusiastic to show off our newer APIs, particularly the asyncio API as well as the use of "2.0 style" queries, and without the awkwardness of the "baked" system which was never intended to be a primary SQLAlchemy API.

In order to test this code I built some local harnesses that run just the SQLAlchemy parts without getting into the whole "make" with containers and all that, so I have not run the "bench.py" harness itself. I think I got everything in there that's needed including the ASYNC attribute, but if there are problems running this please let me know and I will gladly try to find time to get the container part of this running (I'm on Fedora where we use podman, not docker).

thanks for providing this suite and I hope to get feedback!

[1st1]

Thanks Mike, especially for the proper stream of commits and great commit messages.

[zzzeek]

I've run the benches on a variety of combinations to see where we are at, just the three benches mentioned in the Makefile. two of three tests gained speed from 1.3 to 1.4 using the old calling code, one of them lost a bunch for whatever reason, though we got most of it back when using newer APIs. with the new changes I made here, we gain some more modest speed improvements, with asyncio a slight bit even faster (which I credit mostly towards the asyncpg vs. psycopg2 difference, there's no concurrency in place for the way I ran the suite), and 2.0 maybe inches up a bit as we are porting more internal functions to cython.

runs are attached. to summarize the two endpoints, the async API in 2.0 vs. the older code running in 1.3:

2.0 w/ async:

============ Python ============
concurrency:    1
warmup time:    5 seconds
duration:   10 seconds
queries:    insert_movie, get_movie, get_user
benchmarks: sqlalchemy_asyncio

== sqlalchemy_asyncio : insert_movie ==
queries:    1692
qps:        169 q/s
min latency:    4.51ms
avg latency:    5.90ms
max latency:    21.16ms

== sqlalchemy_asyncio : get_movie ==
queries:    2467
qps:        246 q/s
min latency:    3.19ms
avg latency:    4.04ms
max latency:    35.03ms

== sqlalchemy_asyncio : get_user ==
queries:    8595
qps:        859 q/s
min latency:    1.05ms
avg latency:    1.15ms
max latency:    7.40ms

1.3 with old API:

============ Python ============
concurrency:    1
warmup time:    5 seconds
duration:   10 seconds
queries:    insert_movie, get_movie, get_user
benchmarks: sqlalchemy

== sqlalchemy : insert_movie ==
queries:    994
qps:        99 q/s
min latency:    7.40ms
avg latency:    10.05ms
max latency:    31.62ms

== sqlalchemy : get_movie ==
queries:    2967
qps:        296 q/s
min latency:    2.46ms
avg latency:    3.36ms
max latency:    29.92ms

== sqlalchemy : get_user ==
queries:    5700
qps:        570 q/s
min latency:    1.60ms
avg latency:    1.74ms
max latency:    9.57ms

so something is up w/ the get_movie() call, likely some latency regarding the processing of the loader options themselves. since there is no additional SQL emitted, will look into that.

asyncio_api_14.txt asyncio_api_20.txt new_api_14.txt new_api_20.txt old_api_13.txt old_api_14.txt

[zzzeek]

using asyncpg in "sync fallback" mode, where we are using get_event_loop().run_until_complete() for every call into asyncpg, not very big difference in req/sec

============ Python ============
concurrency:    1
warmup time:    5 seconds
duration:   10 seconds
queries:    insert_movie, get_movie, get_user
benchmarks: sqlalchemy

== sqlalchemy : insert_movie ==
queries:    1329
qps:        132 q/s
min latency:    4.69ms
avg latency:    7.51ms
max latency:    21.52ms

== sqlalchemy : get_movie ==
queries:    2438
qps:        243 q/s
min latency:    3.34ms
avg latency:    4.09ms
max latency:    33.93ms

== sqlalchemy : get_user ==
queries:    7617
qps:        761 q/s
min latency:    1.17ms
avg latency:    1.30ms
max latency:    2.06ms