Skip to content

Benchmark SQLAlchemy with pandas.read_sql_query and trim uninformative cases #1133

Closed

Description

@laughingman7743

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+pandas and the other result-file drivers, rows are extracted from the DataFrame or Table that the cursor already read; for 10,000,000 flat rows with unload=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 for awsathena+rest, awsathena+pandas, awsathena+arrow, awsathena+polars, and awsathena+s3fs, the result-file drivers with and without unload=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:

  1. 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.
  2. The initialization suite: asyncio.to_thread() construction took 0.96 to 1.16 times the direct time. Drop it, or keep one scale.
  3. 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.
  4. DictCursor: within 5% of Cursor at every measured scale. Keep it at one scale.
  5. Medium scale: most differences between cases were the one-second polling interval. Keep a small subset for regression checks.

Re-measure after fixes

With these changes, the next run can close #254 and #61 once #1131 and #1132 are resolved.

Activity

  1. laughingman7743 commented on Oct 14, 2026

    @laughingman7743
    MemberAuthor

    Implemented in #1146 and measured in the run of the 4.1.0 release commit; the entry is benchmarks/history/2026-10-13-437276e1.md in #1157.

    • Added: pandas.read_sql_query() through awsathena+rest, +pandas, +arrow, +polars, and +s3fs.
      • The result-file drivers ran with and without unload=true, and with and without chunksize.
      • Wrangler's native reads ran as the reference.
      • 33 jobs at large and xlarge.
      • The REST driver ran at large only and without chunks, because of its paging time.
    • Dropped or reduced: the ThreadPool and native asyncio APIs, the initialization suite, arraysize 100 except for Cursor, DictCursor beyond large flat, and medium beyond a regression subset.
    • Re-measured after the fixes:

    Closing as done.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions