fetch object is slow with sqlalchemy in fastapi

Question

I want to fetch a user from DB(PostgreSQL). While it is a simple query, it takes approximately 2 seconds. SQL logging is this:

2022-08-03 11:08:24,090 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2022-08-03 11:08:24,090 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-08-03 11:08:24,093 INFO sqlalchemy.engine.Engine select current_schema()
2022-08-03 11:08:24,094 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-08-03 11:08:24,097 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2022-08-03 11:08:24,097 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-08-03 11:08:24,099 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-03 11:08:24,102 INFO sqlalchemy.engine.Engine SELECT users.id, users.user_public_id, users.user_name, users.first_name, users.last_name, users.gender, users.user_type, users.agency_code, users.parent_id, users.country_id, users.province_id, users.city_id, users.father_name, users.mentor, users.birth_date, users.avatar, users.bank_id, users.bank_id_status, users.nationality_details, users.legal_authenticated, users.is_suspended, users.suspended_at, users.suspended_by, users.is_deleted, users.deleted_at, users.deleted_by, users.created_at, users.created_by, users.updated_at, users.updated_by 
FROM users 
WHERE users.id = %s
2022-08-03 11:08:24,102 INFO sqlalchemy.engine.Engine [generated in 0.00030s] (UUID('cc4f01cb-6d94-4e5f-b170-d2ee312c515c'),)
2022-08-03 11:08:26,189 INFO sqlalchemy.engine.Engine ROLLBACK

It reveals that until the penultimate line of logging works well. After that, it was taking too long. What is the problem?

@router.get("/user/{item_id}", response_model=user_base_Response_model)
async def fetch_user_by_id(request: Request, item_id: UUID):
    try:
        user_objs = await get_user_by_id(item_id=item_id)
        return response_body(
            True, status.HTTP_200_OK, message()["ACTION_DONE_SUCCESSFULLY"], user_objs
        )
    except:
       .
       .


async def get_user_by_id(item_id: UUID):
    engine, session_maker_object = await initDB()
    async with session_maker_object() as session:
        user_objs = await session.execute(
            select(db_model_user)
            .filter(db_model_user.id == item_id)
        )
        user_objs = user_objs.scalars().unique().first()
    await engine.dispose()
    return user_objs

fetch object is slow with sqlalchemy in fastapi

0 Answers