SQLAlchemy + Pandas very slow when compared to AWS Wrangler

Tested same query that returns more than 2 million lines.

AWS Wrangler takes 1m34s to return a DataFrame.
SQLAlchemy + PyAthena takes 16m37s to return a DataFrame.

See attached notebook for proof and methods.

Also, PyAthena apparently returns an object almost 9% bigger. But this can be due to Pandas, SQLAlchemy, data types and other minor things that I wouldn’t care right now.

#46
https://github.com/laughingman7743/PyAthena/tree/master/benchmarks
https://github.com/laughingman7743/PyAthena#pandascursor

Thank you. I’ve seen all these links and methods before.

But they won’t clarify how to use this with SQLAlchemy. And I need to use SQLAlchemy.

So, is there any way to use PyAthena with SQLAlchemy and Pandas that would match acceptable performance?

Maybe a flag in the URL or something.

I do not have an implementation that uses a combination of SQLAlchemy and Pandas. What is the use case for that?

I have a Pandas application that is independent of database. So SQLAlchemy provides a unified single interface and code doesn’t need to be changed when changing database backend, just the URL.

For now, the current implementation does not allow the use of PandasCursor and SQLAlchemy in combination.
It needs some modification, but by implementing switching the cursor to be used by the connection URL, we may possibly speed up the retrieval of query results.

Are there any guides on how to use it? I’m using SQLALCHEMY query builder to query Athena through pandas read_sql_query(). Or simply the use case I put in code in the notebook attached to this bug report.

Also, I’d suggest to be more explicit about this problem when using PyAthena with SQLALCHEMY and Pandas. Simply to suggest to not use SQLALCHEMY, to bypass SQLALCHEMY, is not a solution.

What do you need a guide for, a guide for PandasCursor?
It has a DB-API2.0 interface, which is the same as the default cursor. The way to fetch data is also the same.

I don't know how to use it in this situation:

conn_str = "awsathena+rest://:@athena.{region_name}.amazonaws.com:443/{schema_name}?work_group={work_group}&compression=snappy"

pyathena_sqlalchemy_conn = sqlalchemy.create_engine(
    url = conn_str.format(
        region_name  = region_name,
        schema_name  = schema_name,
        work_group   = work_group
    ),
    echo_pool = True,
)

pyathena_df = pd.read_sql_query(query,con=pyathena_sqlalchemy_conn)

What should I pass to pandas.read_sql_query() in the con argument in order to use PandasCursor? And how to get it with SQLAlchemy?

Thank you in advance

In the current implementation, SQLAlchemy and PandasCursor cannot be used in combination. As long as you use SQLAlchmey, you cannot change the default cursor.

If you do not use SQLAlchemy, you can use PandasCursor as follows.

cursor = connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                 region_name="REGION_NAME",
                 schema_name="SCHEMA_NAME",
                 work_group="WORK_GROUP",
                 cursor_class=PandasCursor).cursor()
df = cursor.execute(query).as_pandas()

If you want to use PandasCursor in combination with SQLAlchemy, you probably need to modify the part that assembles the arguments of the connection object from the URL.
https://github.com/laughingman7743/PyAthena/blob/master/pyathena/sqlalchemy_athena.py#L302-L332
There is no guarantee that SQLAlchemy will work correctly after changing to PandasCursor.

This new method apparently reduced query time from 16min to 6m35s. But still far from beating AWS Wrangler (without SQLALchemy, indeed) 1m15s:

conn_str = "awsathena+rest://:@athena.{region_name}.amazonaws.com:443/{schema_name}?work_group={work_group}&compression=snappy"

sqlalchemy_connect_args={}
if "awsathena+rest" in conn_str:
    from pyathena.pandas.cursor import PandasCursor
    sqlalchemy_connect_args.update(
        dict(
            cursor_class=PandasCursor
        )
    )

pyathena_sqlalchemy_conn = sqlalchemy.create_engine(
    url = conn_str.format(
        region_name  = region_name,
        schema_name  = schema_name,
        work_group   = work_group
    ),
    connect_args=sqlalchemy_connect_args,
    echo_pool = True,
)

cursor = pyathena_sqlalchemy_conn.raw_connection()

pyathena_df = pd.read_sql_query(query,con=cursor)

pyathena_df.info()

It consists of getting a PandasCursor from SQLAlchemy's raw connections.

But I don't know yet if I can reuse the cursor or should I get a new cursor for each query.

Here is a simplification. Don't need to get a raw_connection() to proceed. Simply using the connection constructed with a PandasCursor gives me 7m14s query time for my 2 million lines query, instead of 17m.

conn_str = "awsathena+rest://:@athena.{region_name}.amazonaws.com:443/{schema_name}?work_group={work_group}&compression=snappy"

sqlalchemy_connect_args={}
if "awsathena+rest" in conn_str:
    from pyathena.pandas.cursor import PandasCursor
    sqlalchemy_connect_args.update(
        dict(
            cursor_class=PandasCursor
        )
    )

pyathena_sqlalchemy_conn = sqlalchemy.create_engine(
    url = conn_str.format(
        region_name  = region_name,
        schema_name  = schema_name,
        work_group   = work_group
    ),
    connect_args=sqlalchemy_connect_args,
    echo_pool = True,
)

pyathena_df = pd.read_sql_query(query,con=pyathena_sqlalchemy_conn)

Still, PyAthena alone or AWS Wrangler (both without SQLAlchemy) run same query in 1m15s, for 2 million rows result.

Villain here is SQLAlchemy or the integration between SQLAlchemy and PyAthena.

I hope this can be solved in the future in order make PyAthena+SQLAlchemy to deliver same performance as PyAthena alone. SQLAlchemy is desired because it makes your code more elegant and succinct, and makes your app look nicer and independent from database engine.

For reference, here is the notebook used in this experiment:
AWS Wrangler × PyAthena × SQLAlchemy.zip.

The PandasCursor itself already has a DataFrame, but it handles the fetching of the DataFrame to comply with the DB-API interface.
https://github.com/laughingman7743/PyAthena/blob/master/pyathena/pandas/result_set.py#L100-L133
The read_sql_query method of Pandas calls the fetchall method of the cursor to create the DataFrame.
https://github.com/pandas-dev/pandas/blob/945c9ed766a61c7d2c0a7cbb251b6edebf9cb7d5/pandas/io/sql.py#L1521-L1602
The process generates an array of tuples from the DataFrame and re-creates the DataFrame. This is a slow process in general.

So indeed all this abstraction from SQLAlchemy comes with a toll.
Anyway, although not optimal, I'm happy to have a performance improvement with PandasCursor.

Add AthenaPandas dialect (refs: #254)

Merge pull request #271 from laughingman7743/add_pandas_dialect

https://towardsdatascience.com/heres-the-most-efficient-way-to-iterate-through-your-pandas-dataframe-4dad88ac92ee

Add AthenaPandas dialect (refs: pyathena-dev#254)

Use to_dict instead of Iterrows (ref #254)

Merge branch 'master' into #254

Merge pull request #284 from laughingman7743/#254

I have implemented various performance improvements.

I think Pandas cursor can get a large result set at the same speed as awswrangler(aws-sdk-pandas) by combining the unload option with our own S3 file system implementation (implemented in v2.18.0).

Related: #644 - Comprehensive benchmark including Wrangler comparison is planned.

The benchmark of the 4.1.0 release commit (#1157, benchmarks/history/2026-10-13-437276e1.md) measured pandas.read_sql_query() through an engine of each PyAthena driver, next to Wrangler. Host: r7g.2xlarge, synchronous, median of 3–5 trials.

Reader	1 million flat rows	10 million flat rows
`awsathena+rest` (default)	230.2 s	not measured
`awsathena+s3fs`	39.2 s	355.0 s
`awsathena+pandas`, CSV	26.5 s	230.8 s
`awsathena+pandas` with `unload=true`	18.3 s	162.6 s
`awsathena+polars` with `unload=true`	11.3 s	92.1 s
`awsathena+arrow`, CSV	10.6 s	68.1 s
`awsathena+arrow` with `unload=true`	7.0 s	50.1 s
Wrangler CSV (native)	12.5 s	95.3 s
Wrangler UNLOAD (native)	3.9 s	21.4 s

The slowness in this issue comes from the default awsathena+rest driver. It pages rows through GetQueryResults, which took 226 seconds for 1 million rows with the plain Cursor as well.
The result-file drivers close most of the gap. awsathena+arrow with unload=true (for example awsathena+arrow://...?unload=true in the connection URL, or cursor_kwargs={"unload": True}) was 33 times faster than the default driver. It also beat Wrangler CSV, and it took about 2 times Wrangler UNLOAD.
The rest of the difference comes from read_sql_query() itself, which builds the DataFrame from Python rows. PyAthena's native reads do not: PandasCursor with unload=True returned the 10 million rows as a DataFrame in 19.0 seconds, against Wrangler's 21.4.
Memory: with chunksize=100000, the pandas, Polars, and S3FS drivers stayed at 590–1,259 MiB for 10 million rows.

For read_sql_query() today, use awsathena+arrow with unload=true, or awsathena+polars/awsathena+pandas with unload=true and chunksize when memory matters; for the fastest pandas read, use PandasCursor directly. RESULTS.md in #1157 lists these recommendations.

awsathena+pandas, the SQLAlchemy and pandas path of this issue, still takes 18.3 seconds for 1 million rows: 1.5 times Wrangler CSV and about 4 to 5 times Wrangler CTAS or UNLOAD. Most of that is PandasCursor's row output, which builds a dictionary per row; #1161 tracks it. This issue stays open until #1161 is measured.

The benchmark of PyAthena 4.2.0 (#1288, #1310, benchmarks/history/2026-10-19-9e99caa8.md) measured pandas.read_sql_query() again after #1161 (#1167, #1177) made the row output of PandasCursor and PolarsCursor faster. Host: r7g.2xlarge, synchronous, median of 3–5 trials, total time from execute through consumption.

Reader	1 million flat rows	10 million flat rows
`awsathena+rest` (default)	227.0 s (4.1.0: 230.2 s)	not measured
`awsathena+pandas`, CSV	14.8 s (26.5 s)	132.5 s (230.8 s)
`awsathena+pandas` with `unload=true`	7.0 s (18.3 s)	50.7 s (162.6 s)
`awsathena+polars` with `unload=true`	6.7 s (11.3 s)	48.7 s (92.1 s)
`awsathena+arrow` with `unload=true`	6.8 s (7.0 s)	50.9 s (50.1 s)
Wrangler CSV (native)	12.4 s	108.9 s
Wrangler UNLOAD (native)	3.9 s	23.4 s

The SQLAlchemy and pandas path of this issue now beats Wrangler CSV. awsathena+pandas with unload=true took 7.0 seconds for 1 million rows and 50.7 seconds for 10 million, against 12.4 and 108.9 seconds for Wrangler CSV.
The remaining difference to Wrangler UNLOAD, about 2 times, comes from read_sql_query() itself, which builds the DataFrame from Python rows. PandasCursor with unload=True returns the same 10 million rows as a DataFrame in 19.8 seconds, against Wrangler UNLOAD's 23.4.
The default awsathena+rest driver is still slow, because it pages rows through GetQueryResults. Use a result-file driver with unload=true.
Memory: with chunksize=100000, the pandas and Polars drivers stayed at 1,032–1,054 MiB for 10 million flat rows through UNLOAD.

benchmarks/RESULTS.md lists the recommendation for read_sql_query(). Closing this issue with #1310.

Navigation Menu

Repository navigation

SQLAlchemy + Pandas very slow when compared to AWS Wrangler #254

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

SQLAlchemy + Pandas very slow when compared to AWS Wrangler #254

Description

Activity

laughingman7743 commented on Nov 25, 2021

avibrazil commented on Nov 25, 2021

laughingman7743 commented on Nov 26, 2021

avibrazil commented on Nov 26, 2021

laughingman7743 commented on Nov 26, 2021

avibrazil commented on Nov 26, 2021

laughingman7743 commented on Nov 26, 2021

avibrazil commented on Nov 26, 2021

laughingman7743 commented on Nov 26, 2021

laughingman7743 commented on Nov 26, 2021

avibrazil commented on Nov 26, 2021

avibrazil commented on Nov 26, 2021

avibrazil commented on Nov 26, 2021

laughingman7743 commented on Nov 26, 2021

avibrazil commented on Nov 26, 2021

laughingman7743 commented on Jan 25, 2022

laughingman7743 commented on Dec 18, 2022

laughingman7743 commented on Jan 6, 2026

laughingman7743 commented on Oct 14, 2026

laughingman7743 commented on Oct 19, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions