Skip to content

Instantly share code, notes, and snippets.

@WilliamStam

WilliamStam/benchy_oracledb.py

Created January 12, 2024 13:06
Show Gist options
  • Save WilliamStam/63c1ab8f49018c4526c63a2cd3b19088 to your computer and use it in GitHub Desktop.
Save WilliamStam/63c1ab8f49018c4526c63a2cd3b19088 to your computer and use it in GitHub Desktop.
Download ZIP
Sqlalchemy oracle connections
Raw
benchy_oracledb.py
# Sqlalchemy Async
# Records: 164
# Time: 15.389007091522217
# Average: 0.30778014183044433
# -
# Sqlalchemy Sync
# Records: 164
# Time: 15.195691585540771
# Average: 0.30391383171081543
# -
# Oracledb Sync
# Records: 164
# Time: 6.3568267822265625
# Average: 0.12713653564453126
# -
# Oracledb Async
# Records: 164
# Time: 4.254000425338745
# Average: 0.0850800085067749
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="xxx",
password="xxx",
host="xxx",
schema="xxx",
port=1521,
database="xxx"
)
query = "SELECT * FROM TABLE" # 164 records,
loops = 50
results: list[Result] = list()
# ----------------------------------------------------------------------
# Sqlalchemy Async
# ----------------------------------------------------------------------
result = Result(label="Sqlalchemy Async")
engine = create_async_engine(
URL.create(
drivername=settings.driver,
username=settings.username,
password=settings.password,
host=settings.host,
port=settings.port,
database=settings.database,
)
)
async with engine.connect() as session:
x = await session.execute(text(query))
x.fetchall()
for i in range(0,loops):
start = time.time()
records = await session.execute(text(query))
r = 0
for record in records.all():
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:
x = session.execute(text(query))
x.fetchall()
for i in range(0, loops):
start = time.time()
records = session.execute(text(query))
r = 0
for record in records.all():
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")
for i in range(0, loops):
connection = oracledb.connect(
user=settings.username,
password=settings.password,
host=settings.host,
port=settings.port,
service_name=settings.database
)
with connection:
with connection.cursor() as cursor:
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")
for i in range(0, loops):
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:
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())
Raw
benchy_oracledb_vs_cx_oracle.py
# Sqlalchemy oracledb Sync
# Records: 164
# Time: 14.694408893585205
# Average: 0.2938881778717041
# -
# Sqlalchemy cx_oracle sync
# Records: 164
# Time: 17.163243293762207
# Average: 0.34326486587524413
# -
# cx_oracle Sync
# Records: 164
# Time: 7.658784627914429
# Average: 0.15317569255828858
# -
# Oracledb Sync
# Records: 164
# Time: 4.6097352504730225
# Average: 0.09219470500946045
import asyncio
import dataclasses
import time
import oracledb
import cx_Oracle
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="xxx",
password="xxx",
host="xxx",
schema="xxx",
port=1521,
database="xxx"
)
query = "SELECT * FROM Table"
loops = 50
results: list[Result] = list()
# ----------------------------------------------------------------------
# sqlalchemy sync
# ----------------------------------------------------------------------
result = Result(label="Sqlalchemy oracledb 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:
x = session.execute(text(query))
x.fetchall()
for i in range(0, loops):
start = time.time()
records = session.execute(text(query))
r = 0
for record in records.all():
r = r + 1
pass
result.records = r
result.times.append(time.time() - start)
print(result)
results.append(result)
# ----------------------------------------------------------------------
# sqlalchemy sync
# ----------------------------------------------------------------------
result = Result(label="Sqlalchemy cx_oracle sync")
engine = create_engine(
URL.create(
drivername="oracle+cx_oracle",
username=settings.username,
password=settings.password,
host=settings.host,
port=settings.port,
database=settings.database,
)
)
with engine.connect() as session:
for i in range(0, loops):
start = time.time()
records = session.execute(text(query))
r = 0
for record in records.all():
r = r + 1
pass
result.records = r
result.times.append(time.time() - start)
print(result)
results.append(result)
# ----------------------------------------------------------------------
# cx_oracle sync
# ----------------------------------------------------------------------
result = Result(label="cx_oracle Sync")
for i in range(0, loops):
connection = cx_Oracle.connect(
user=settings.username,
password=settings.password,
dsn=f"{settings.host}:{settings.port}/{settings.database}"
)
with connection:
with connection.cursor() as cursor:
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 sync
# ----------------------------------------------------------------------
result = Result(label="Oracledb Sync")
for i in range(0, loops):
connection = oracledb.connect(
user=settings.username,
password=settings.password,
host=settings.host,
port=settings.port,
service_name=settings.database
)
with connection:
with connection.cursor() as cursor:
cursor.execute(query)
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)
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())
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment