为何SQLAlchemy异步查询比同步查询慢？FastAPI场景测试

环境版本 #

Python==3.11
psyconpg-binary==2.9.7
asyncpg==0.28.0
SQLAlchemy==2.0.25

测试脚本 #

import asyncio
from datetime import datetime

from pyinstrument import Profiler
from sqlalchemy import DateTime, String, create_engine, select
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):  # type:ignore
    pass


class Device(Base):
    __tablename__ = "device"

    id: Mapped[int] = mapped_column(primary_key=True)
    date_created: Mapped[datetime] = mapped_column(DateTime())
    mac: Mapped[str] = mapped_column(String(50))


stmt = select(Device).where(Device.mac.in_("ff-ff-ff-ff-ff-ff"))


def foo1():
    t = datetime.now()
    engine = create_engine("postgresql+psycopg2://user:password@localhost:5432/db_name")
    with Session(engine) as session:
        result = session.execute(stmt).all()
    engine.dispose()
    print("Execute first synchronous query ", (datetime.now() - t).microseconds * 0.001)

    t = datetime.now()

    with Session(engine) as session:
        result = session.execute(stmt).all()
    engine.dispose()
    print("Execute second synchronous query ", (datetime.now() - t).microseconds * 0.001)


async def foo2():
    t = datetime.now()

    engine = create_async_engine("postgresql+asyncpg://user:password@localhost:5432/db_name")
    async_session = async_sessionmaker(engine, expire_on_commit=False)
    async with async_session() as session:
        result = await session.execute(stmt)
    await engine.dispose()
    print("Execute first asynchronous query ", (datetime.now() - t).microseconds * 0.001)

    t = datetime.now()
    profiler = Profiler(async_mode="enabled")
    profiler.start()
    async with async_session() as session:
        result = await session.execute(stmt)
    await engine.dispose()
    profiler.stop()
    print(profiler.print())
    print("Execute second asynchronous query ", (datetime.now() - t).microseconds * 0.001)


foo1()
asyncio.run(foo2())

执行结果（单位：毫秒） #

Execute first synchronous query  48.031
Execute second synchronous query  7.0600000000000005
Execute first asynchronous query  47.852000000000004
Execute second asynchronous query  29.87

性能分析结果 #

0.043 MainThread  <thread>:140457073989440
├─ 0.042 <module>  test.py:1
│  └─ 0.042 run  asyncio/runners.py:160
│        [25 frames hidden]  asyncio, hmac, <built-in>, re, asyncp...
│           0.040 Handle._run  asyncio/events.py:78
│           │  0.033 _SelectorSocketTransport._read_ready__data_received  asyncio/selector_events.py:991
│           │  ├─ 0.011 [self]  asyncio/selector_events.py
│           ├─ 0.006 foo2  test.py:44
│           │  ├─ 0.003 AsyncSession.execute  sqlalchemy/ext/asyncio/session.py:429
│           │  │     [17 frames hidden]  sqlalchemy, asyncpg, asyncio
│           │  ├─ 0.002 AsyncEngine.dispose  sqlalchemy/ext/asyncio/engine.py:1120
│           │  │     [4 frames hidden]  sqlalchemy, asyncpg
│           │  └─ 0.001 [self]  test.py
└─ 0.001 Session.execute  sqlalchemy/orm/session.py:2201
      [10 frames hidden]  sqlalchemy

在FastAPI项目中使用SQLAlchemy ORM时，发现相同查询请求以同步和异步方式执行时耗时存在明显差异：第二次同步查询仅耗时约7毫秒，而第二次异步查询耗时近30毫秒。尝试调整连接配置但情况未发生改变，相关参考未找到有效解决办法。

内容的提问来源于stack exchange，提问作者Timur Usmanov