Python Drivers for SQL Benchmark

Description

The solution compares the performance of pyodbc and pyodbc with SQLAlchemy vs mssql-python for various SELECT statements against SQL Server 2025 and Azure SQL. The dataset used is AdventureWorks2022.

Benchmark Setup

Client Machine

• Windows 11 Pro (64-bit) 24H2
• Intel Core i7 (12th Gen), 32 GB RAM, NVMe SSD
• Python 3.11.4 (64-bit).

Database

• SQL Server 2025 Developer Preview (Microsoft SQL Server 2025 (CTP2.0) - 17.0.700.9 (X64), May 8, 2025)
• Azure SQL Database General Purpose (Free - General Purpose - Serverless: Gen5, 2 vCores, Max storage: 32 GB).

Database backup file

• AdventureWorks2022.bak OLTP latest

Driver Versions

• mssql-python: v0.13.0
• pyodbc: v5.2.0
• SQLAlchemy v2.0.43

Tools

• custom benchmarking function with parameter for run count.

Modules in the solution

• sql_client: contains relational database service configuration classes.
• query: container for statis queries to evaluate performance with.
• benchmark: core module defining the benchmarking function.
• driver_benchmark_app: core app file to run everyhtin.

How to run

1. Install SQL Server 2025 Preview.
2. Restore AdventureWorks2022 from backup.
3. Deploy an Azure SQL instance using the template in the az_template folder.
4. Migrate AW2022 to Azure SQL using "Deploy Database to Azure SQL" functionality.
5. git clone this repo and open in VS Code.
6. Ctrl + Shift + P and create a Python virtual environment. Ensure requirements.txt stays in the root folder. The environment creation wizard will prompt you to include the packages listed in the file.
7. Create .env file in the root folder with the following variables:
   • When testing against SQL Server 2025 keep the Azure SQL set of variables commented and vice-versa. The database name always stays the same.
   • Configure the values for your SQL Server and Azure SQL accordingly.
8. Make sure your venv is activated.
9. Run driver_benchmark_app.py.