driver_benchmark_app.py

from sql_client import (
    RDSConfigPyODBC,
    RDSConfigPyODBCRaw,
    RDSConfigMSSQLPython
    )
from query import QueryContainer
from benchmark import PerformanceTester as pt

# pyodbc with sqlalchemy
pyodbc_cfg = RDSConfigPyODBC()
pyodbc_engine = pyodbc_cfg.get_engine()
pyodbc_sqlalchemy_connection = pyodbc_engine.connect()

# Raw pyodbc
pyodbc_raw_cfg = RDSConfigPyODBCRaw()
pyodbc_raw_connection = pyodbc_raw_cfg.get_connection()

# mssql-python
mssql_python_cfg = RDSConfigMSSQLPython()
mssql_python_connection = mssql_python_cfg.get_connection()

connections = [
    ('pyodbc + SQLAlchemy', pyodbc_sqlalchemy_connection, 'pyodbc + SQLAlchemy'),
    ('pyodbc raw', pyodbc_raw_connection, 'pyodbc-raw'),
    ('mssql-python', mssql_python_connection, 'mssql-python')
]

queries = QueryContainer.get_query()

print('=' * 80)
print('MSSQL Driver Performance Benchmark - AdventureWorks2022')
print('=' * 80)

driver_results = {}
results = []

for i, (query_name, query) in enumerate(queries.items()):
    current_query_index = i+1

    print('-' * 80)
    print(f'\n[Query {current_query_index}] {query_name}')

    query_results = []

    for driver_name, connection, driver_name in connections:
        result = pt.benchmark_query(connection, query, driver_name)
        query_results.append((driver_name, result))

    for driver_name, result in query_results:
        driver_results[driver_name] = result
        print(f'\n{driver_name} Results:')
        print(f"  Rows returned: {result['rows']}")
        print(f"  Average time:  {result['avg_time']:.4f}s")

    baseline_time = driver_results['pyodbc + SQLAlchemy']['avg_time']

    print('\nPerformance Comparison (vs pyodbc + SQLAlchemy):')
    for driver_name in ['pyodbc-raw', 'mssql-python']:
        driver_time = driver_results[driver_name]['avg_time']
        speedup = baseline_time / driver_time
        print(f"  {driver_name}: {speedup:.2f}x {'faster' if speedup > 1 else 'slower'}")

    print('-' * 80)

    results.append({
        'query_name': query_name,
        'query_index': current_query_index,
        'rows': driver_results['pyodbc + SQLAlchemy']['rows'],
        'pyodbc_sqlalchemy_avg': driver_results['pyodbc + SQLAlchemy']['avg_time'],
        'pyodbc_raw_avg': driver_results['pyodbc-raw']['avg_time'],
        'mssql_avg': driver_results['mssql-python']['avg_time'],
        'speedup_raw': baseline_time / driver_results['pyodbc-raw']['avg_time'],
        'speedup_mssql': baseline_time / driver_results['mssql-python']['avg_time']
    })

total_sqlalchemy_time = sum(r['pyodbc_sqlalchemy_avg'] for r in results)
total_raw_time = sum(r['pyodbc_raw_avg'] for r in results)
total_mssql_time = sum(r['mssql_avg'] for r in results)

print('\nTotal execution time:')
print(f"  pyodbc + SQLAlchemy:  {total_sqlalchemy_time:.4f}s")
print(f"  pyodbc (raw):         {total_raw_time:.4f}s")
print(f"  mssql-python:         {total_mssql_time:.4f}s")

speedup_raw = total_sqlalchemy_time / total_raw_time
speedup_mssql = total_sqlalchemy_time / total_mssql_time

print(f"\n  pyodbc (raw) vs SQLAlchemy:    {speedup_raw:.2f}x {'faster' if speedup_raw > 1 else 'slower'}")
print(f"  mssql-python vs SQLAlchemy:    {speedup_mssql:.2f}x {'faster' if speedup_mssql > 1 else 'slower'}")

print('\nQuery-by-query results:')
print(f"{'Query':<5} {'Name':<30} {'Rows':>10} {'SQL+pyodbc':>12} {'Raw pyodbc':>12} {'mssql-py':>12}")
print("-" * 95)
for r in results:
    print(f"{r['query_index']:<5} {r['query_name']:<30} {r['rows']:>10,} "
          f"{r['pyodbc_sqlalchemy_avg']:>11.4f}s {r['pyodbc_raw_avg']:>11.4f}s {r['mssql_avg']:>11.4f}s")

# Find best performer for each query
print('\nBest performer by query:')
for r in results:
    times = [
        ('pyodbc + SQLAlchemy', r['pyodbc_sqlalchemy_avg']),
        ('pyodbc (raw)', r['pyodbc_raw_avg']),
        ('mssql-python', r['mssql_avg'])
    ]
    fastest = min(times, key=lambda x: x[1])
    print(f"  Query {r['query_index']}: {fastest[0]} ({fastest[1]:.4f}s)")

print("\n" + "=" * 80)

# Cleanup

for _, conn, name in connections:
    try:
        conn.close()
        print(f'\nConnection {name} closed.')
    except Exception as e:
        print(f"Warning: failed to close connection '{name}': {e}")