This directory contains benchmark scripts for testing the performance of various database operations using pyodbc and mssql_python. The goal is to evaluate and compare the performance of these libraries for common database operations.
Comprehensive benchmarks using the richbench framework for detailed performance analysis.
Standalone script that tests real-world queries against AdventureWorks2022 database with statistical analysis.
- To measure the efficiency of
pyodbcandmssql_pythonin handling database operations. - To identify performance bottlenecks and optimize database interactions.
- To ensure the reliability and scalability of the libraries under different workloads.
-
Set Up the Environment Variable:
- Ensure you have a running SQL Server instance.
- Set the
DB_CONNECTION_STRINGenvironment variable with the connection string to your database. For example:export DB_CONNECTION_STRING="Server=your_server;Database=AdventureWorks2022;UID=your_user;PWD=your_password;"
-
Install Richbench - Benchmarking Tool:
pip install richbench
-
Run the Benchmarks:
- Execute richbench from the parent folder (mssql-python):
richbench benchmarks
- Results will be displayed in the terminal with detailed performance metrics.
- Execute richbench from the parent folder (mssql-python):
This script tests performance with real-world queries from the AdventureWorks2022 database.
-
Prerequisites:
- AdventureWorks2022 database must be available
- Both
pyodbcandmssql-pythonmust be installed - Update the connection string in the script if needed
-
Run from project root:
python benchmarks/perf-benchmarking.py
-
Features:
- Runs each query multiple times (default: 5 iterations)
- Calculates average, min, max, and standard deviation
- Provides speedup comparisons between libraries
- Tests various query patterns:
- Complex joins with aggregations
- Large dataset retrieval (10K+ rows)
- Very large dataset (1.2M rows)
- CTEs and subqueries
- Detailed summary tables and conclusions
-
Output: The script provides:
- Progress indicators during execution
- Detailed results for each benchmark
- Summary comparison table
- Overall performance conclusion with speedup factors
- Comprehensive Benchmarks: Includes SELECT, INSERT, UPDATE, DELETE, complex queries, stored procedures, and transaction handling.
- Error Handling: Each benchmark function is wrapped with error handling to ensure smooth execution.
- Progress Messages: Clear progress messages are printed during execution for better visibility.
- Automated Setup and Cleanup: The script automatically sets up and cleans up the database environment before and after the benchmarks.
- Statistical Analysis: Multiple iterations with avg/min/max/stddev calculations
- Real-World Queries: Tests against AdventureWorks2022 with production-like queries
- Automatic Import Resolution: Correctly imports local
mssql_pythonpackage - Comprehensive Reporting: Detailed comparison tables and performance summaries
- Speedup Calculations: Clear indication of performance differences
- Ensure the database user has the necessary permissions to create and drop tables and stored procedures.
- The
bench_mssql.pyscript uses permanent tables prefixed withperfbenchmark_for benchmarking purposes. - A stored procedure named
perfbenchmark_stored_procedureis created and used during the benchmarks. - The
perf-benchmarking.pyscript connects to AdventureWorks2022 and requires read permissions only.