Repository navigation
Benchmark SQLAlchemy with pandas.read_sql_query and trim uninformative cases #1133
Description
Summary
Add the SQLAlchemy and pandas.read_sql_query() path to the benchmark harness, and drop the cases that the 4.0.0 run (#644, results in #1130) showed to carry no information, before the next run.
Add: SQLAlchemy with pandas.read_sql_query()
#254 reports that pandas.read_sql_query() through a SQLAlchemy engine is much slower than AWS SDK for pandas (Wrangler).
The 4.0.0 run did not measure this path.
read_sql_query() builds the DataFrame from fetchall(), so it pays the row-output cost of the selected driver on top of the driver's own read:
- With
awsathena+rest(the default Cursor), rows come from GetQueryResults paging; the run measured 213 seconds for 1,000,000 flat rows at arraysize 1000. - With
awsathena+pandasand the other result-file drivers, rows are extracted from the DataFrame or Table that the cursor already read; for 10,000,000 flat rows withunload=True, the run measured 130 seconds of row output from PandasCursor against 19 seconds for its native DataFrame.
Proposed cases, synchronous API, flat and nested shapes, large and xlarge:
pandas.read_sql_query()with engines forawsathena+rest,awsathena+pandas,awsathena+arrow,awsathena+polars, andawsathena+s3fs, the result-file drivers with and withoutunload=true;pandas.read_sql_query(..., chunksize=...)for the same engines, to check whether memory stays bounded;- the existing Wrangler native cases as the reference.
The result should show how far each driver closes the gap that #254 reports, and which driver and options to recommend for read_sql_query() users.
Drop or reduce
These measured no difference in the 4.0.0 run:
- ThreadPool and native asyncio APIs in the single-query suite: the median spread among sync, thread, and aio was 1.7% over 128 cases. Keep them in the concurrent suite.
- The initialization suite:
asyncio.to_thread()construction took 0.96 to 1.16 times the direct time. Drop it, or keep one scale. - Arraysize 100 for result-file cursors: arraysize changed row output by less than the noise for S3FSCursor (large flat: 40.2 s at 100, 39.7 s at 1000) and the DataFrame cursors. Keep arraysize 100 only for Cursor, where it sets the number of pages.
- DictCursor: within 5% of Cursor at every measured scale. Keep it at one scale.
- Medium scale: most differences between cases were the one-second polling interval. Keep a small subset for regression checks.
Re-measure after fixes
- Chunked UNLOAD for PolarsCursor and PandasCursor (Chunked UNLOAD reads do not bound memory in PolarsCursor and PandasCursor #1131).
- Unchunked PandasCursor CSV against Wrangler CSV (PandasCursor reads unchunked CSV results slower than AWS SDK for pandas #1132).
- PandasCursor UNLOAD of nested rows with
dtype_backend="pyarrow", which reduced memory in a local test from 1,041 to 234 MiB for 2,000,000 rows.
With these changes, the next run can close #254 and #61 once #1131 and #1132 are resolved.
Activity
Benchmark read_sql_query and trim the next benchmark selection
laughingman7743 commentedon Oct 14, 2026
Implemented in #1146 and measured in the run of the 4.1.0 release commit; the entry is
benchmarks/history/2026-10-13-437276e1.mdin #1157.pandas.read_sql_query()throughawsathena+rest,+pandas,+arrow,+polars, and+s3fs.unload=true, and with and withoutchunksize.dtype_backend="pyarrow": 5,897 MiB against 19,664 MiB.Closing as done.