benchmark oracle connections

benchy.py

import asyncio
import dataclasses
import time
import oracledb

from sqlalchemy import text, URL
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy import create_engine


@dataclasses.dataclass
class DatabaseConfig:
    driver: str | None = None
    username: str | None = None
    password: str | None = None
    host: str | None = None
    port: int | None = None
    database: str | None = None
    schema: str | None = None


@dataclasses.dataclass
class Result():
    label: str | None = None
    times: list[float] = dataclasses.field(default_factory=list)
    records: int = 0

    def average(self) -> float:
        return sum(self.times) / len(self.times)

    def total(self) -> float:
        return sum(self.times)



async def main():
    settings = DatabaseConfig(
        driver="oracle+oracledb",
        username="xx",
        password="xx",
        host="xx",
        schema="xx",
        port=1521,
        database="xx"
    )
    query = "SELECT * FROM TABLE"
    loops = 50 

    results: list[Result] = list()

    # ----------------------------------------------------------------------
    # Sqlalchemy Async
    # ----------------------------------------------------------------------
    result = Result(label="Sqlalchemy Async")
    class SqlalchemyAsync:
        def __init__(self, config: DatabaseConfig):
            self.config = config
            self.session = None

        async def __aenter__(self):
            engine = create_async_engine(
                URL.create(
                    drivername=self.config.driver,
                    username=self.config.username,
                    password=self.config.password,
                    host=self.config.host,
                    port=self.config.port,
                    database=self.config.database,
                ),
            )
            session_maker = async_sessionmaker(bind=engine)
            self.session = session_maker()
            return self.session

        async def __aexit__(self, exc_type, exc, tb):
            await self.session.close()

    async with SqlalchemyAsync(settings) as session:
        await session.execute(text(query))

        for i in range(0,loops):
            start = time.time()
            records = await session.execute(text(query))
            r = 0
            for record in records:
                r = r + 1
                pass
            result.records = r
            result.times.append(time.time() - start)
    print(result)
    results.append(result)



    # ----------------------------------------------------------------------
    # sqlalchemy sync
    # ----------------------------------------------------------------------

    result = Result(label="Sqlalchemy Sync")
    engine = create_engine(URL.create(
        drivername=settings.driver,
        username=settings.username,
        password=settings.password,
        host=settings.host,
        port=settings.port,
        database=settings.database,
    ))
    with engine.connect() as session:
        session.execute(text(query))
        for i in range(0, loops):
            start = time.time()
            records = session.execute(text(query))
            r = 0
            for record in records:
                r = r + 1
                pass
            result.records = r
            result.times.append(time.time() - start)


    print(result)
    results.append(result)

    # ----------------------------------------------------------------------
    # oracledb sync
    # ----------------------------------------------------------------------
    result = Result(label="Oracledb Sync")
    connection = oracledb.connect(
        user=settings.username,
        password=settings.password,
        host=settings.host,
        port=settings.port,
        service_name=settings.database
    )
    times = []
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(query)
            for i in range(0, loops):
                start = time.time()
                records = cursor.execute(query)
                r = 0
                for record in records:
                    r = r + 1
                    pass
                result.records = r
                result.times.append(time.time() - start)
    print(result)
    results.append(result)

    # ----------------------------------------------------------------------
    # oracledb async
    # ----------------------------------------------------------------------
    result = Result(label="Oracledb Async")
    connection = await oracledb.connect_async(
        user=settings.username,
        password=settings.password,
        host=settings.host,
        port=settings.port,
        service_name=settings.database
    )
    async with connection:
        await connection.execute(query)
        async with connection.cursor() as cursor:
            await cursor.execute(query)
            for i in range(0, loops):
                start = time.time()
                await cursor.execute(query)
                r = 0
                async for record in cursor:
                    r = r + 1
                result.records = r
                result.times.append(time.time() - start)
    print(result)
    results.append(result)

    print("-"*80)
    for result in results:
        print(result.label)
        print("  Records:",result.records)
        print("  Time:",result.total())
        print("  Average:",result.average())
        print("-")

if __name__ == "__main__":
    asyncio.run(main())

results_small_table.txt

Sqlalchemy Async
  Records: 164
  Time: 13.698800086975098
  Average: 0.27397600173950193
-
Sqlalchemy Sync
  Records: 164
  Time: 15.192358016967773
  Average: 0.3038471603393555
-
Oracledb Sync
  Records: 164
  Time: 6.411708354949951
  Average: 0.128234167098999
-
Oracledb Async
  Records: 164
  Time: 6.351839303970337
  Average: 0.12703678607940674
-