query-performance-analyzer 0.4.0

A library with the Lakehouse Framework

Query Load testing

This project is a benchmarking tool that compares query performance across different SQL Warehousing solutions.

Current support out of the box for:

• ODBC DBs e.g. Synapse Serverless
• Databricks SQL Warehousing

It supports concurrent and sequential execution modes to evaluate performance, caching effects, and scalability.

---

⚙️ Setup

pip install query-performance-analyzer

Dependencies include:

• pyodbc
• azure-identity
• databricks-sql-connector
• PyYAML

Configure config file

The project uses a structured configuration format you can use a yaml, py, json file as you want to config it

Python

Create a config.py file:

import os

CONFIG = {
    "CONNECTIONS": {
        "Connection1": {
            "CONNECTION_STRING": "DRIVER={ODBC Driver 18 for SQL Server};SERVER=server.sql.azuresynapse.net,1433;DATABASE=databases;UID=uid;PWD=pwd;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;",
            "SOURCE": "ODBC"
        },
        "Connection2": {
            "DATABRICKS_TOKEN": os.getenv("DATABRICKS_TOKEN"),
            "DATABRICKS_SERVER": os.getenv("DATABRICKS_HOST"),
            "DATABRICKS_HTTP_PATH": "/sql/1.0/warehouses/148ccb90800933a1",
            "SOURCE": "Databricks"
        }
    },
    "QUERIES": {
        "Plan1": [
            "SELECT * FROM nikkthegreek.bronze.sales_transactions",
            "SELECT * FROM nikkthegreek.bronze.sales_transactions LIMIT 10",
            "SELECT COUNT(*) FROM nikkthegreek.bronze.sales_transactions"
        ],
        "Plan2": [
            "SELECT * FROM nikkthegreek.bronze.sales_transactions",
            "SELECT * FROM nikkthegreek.bronze.sales_transactions LIMIT 10",
            "SELECT COUNT(*) FROM nikkthegreek.bronze.sales_transactions"
        ],
        "Plan3": [
            "SELECT * FROM nikkthegreek.bronze.sales_transactions",
            "SELECT * FROM nikkthegreek.bronze.sales_transactions LIMIT 10",
            "SELECT COUNT(*) FROM nikkthegreek.bronze.sales_transactions"
        ]
    },
    "EXPERIMENTS": {
        "Experiment1": {
            "CONNECTION": "Connection1",
            "PLAN": "Plan1",
            "SEQUENTIAL_RUNS_PER_QUERY": 3,
            "PARALLEL_RUNS": 5
        },
        "Experiment2": {
            "CONNECTION": "Connection2",
            "PLAN": "Plan2",
            "SEQUENTIAL_RUNS_PER_QUERY": 2,
            "PARALLEL_RUNS": 4
        },
        "Experiment3": {
            "CONNECTION": "Connection1",
            "PLAN": "Plan3",
            "SEQUENTIAL_RUNS_PER_QUERY": 4,
            "PARALLEL_RUNS": 6
        }
    }
}

YAML

Create a config.yaml file with environment variable support:

# LakePerf Configuration - YAML Format
CONNECTIONS:
  Connection1:
    CONNECTION_STRING: "DRIVER={ODBC Driver 18 for SQL Server};SERVER=server.sql.azuresynapse.net,1433;DATABASE=databases;UID=uid;PWD=pwd;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;",
    SOURCE: "ODBC"
  
  Connection2:
    DATABRICKS_TOKEN: "${DATABRICKS_TOKEN}"  # Environment variable
    DATABRICKS_SERVER: "${DATABRICKS_HOST}"   # Environment variable
    DATABRICKS_HTTP_PATH: "/sql/1.0/warehouses/148ccb90800933a1"
    SOURCE: "Databricks"

QUERIES:
  Plan1:
    - "SELECT * FROM nikkthegreek.bronze.sales_transactions"
    - "SELECT * FROM nikkthegreek.bronze.sales_transactions LIMIT 10"
    - "SELECT COUNT(*) FROM nikkthegreek.bronze.sales_transactions"
  
  Plan2:
    - "SELECT * FROM nikkthegreek.bronze.sales_transactions"
    - "SELECT * FROM nikkthegreek.bronze.sales_transactions LIMIT 10"
    - "SELECT COUNT(*) FROM nikkthegreek.bronze.sales_transactions"

EXPERIMENTS:
  Experiment1:
    CONNECTION: "Connection1"
    PLAN: "Plan1"
    SEQUENTIAL_RUNS_PER_QUERY: 3
    PARALLEL_RUNS: 5
  
  Experiment2:
    CONNECTION: "Connection2"
    PLAN: "Plan2"
    SEQUENTIAL_RUNS_PER_QUERY: 2
    PARALLEL_RUNS: 4

JSON

Create a config.json file:

{
  "CONNECTIONS": {
    "Connection1": {
      "CONNECTION_STRING": "DRIVER={ODBC Driver 18 for SQL Server};SERVER=server.sql.azuresynapse.net,1433;DATABASE=databases;UID=uid;PWD=pwd;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;",
      "SOURCE": "ODBC"
    },
    "Connection2": {
      "DATABRICKS_TOKEN": "your-databricks-token",
      "DATABRICKS_SERVER": "your-databricks-host",
      "DATABRICKS_HTTP_PATH": "/sql/1.0/warehouses/148ccb90800933a1",
      "SOURCE": "Databricks"
    }
  },
  "QUERIES": {
    "Plan1": [
      "SELECT * FROM nikkthegreek.bronze.sales_transactions",
      "SELECT * FROM nikkthegreek.bronze.sales_transactions LIMIT 10",
      "SELECT COUNT(*) FROM nikkthegreek.bronze.sales_transactions"
    ],
    "Plan2": [
      "SELECT * FROM nikkthegreek.bronze.sales_transactions",
      "SELECT * FROM nikkthegreek.bronze.sales_transactions LIMIT 10",
      "SELECT COUNT(*) FROM nikkthegreek.bronze.sales_transactions"
    ]
  },
  "EXPERIMENTS": {
    "Experiment1": {
      "CONNECTION": "Connection1",
      "PLAN": "Plan1",
      "SEQUENTIAL_RUNS_PER_QUERY": 3,
      "PARALLEL_RUNS": 5
    },
    "Experiment2": {
      "CONNECTION": "Connection2",
      "PLAN": "Plan2",
      "SEQUENTIAL_RUNS_PER_QUERY": 2,
      "PARALLEL_RUNS": 4
    }
  }
}

▶️ Running the Benchmark

Option1: Use the new structured runner for custom experiments.

# Import runner
from perf import runner

# Load configuration from conf.json
import json

with open('conf.json', 'r') as f:
    conf_json = json.load(f)

# Load configuration from conf.yaml
import yaml

with open('conf.yaml', 'r') as f:
    conf_yaml = yaml.safe_load(f)

# Load configuration from conf.py
import conf
conf_py = conf.CONFIG

# Execute runner with experiment name from conf, it returns results as dict
runner.run_experiment("Experiment2", conf=conf_py)

Option2: Use the CMD line

positional arguments: config Path to the YAML or JSON configuration file experiment Name of the experiment to run (must be defined in the config file)

options: -h, --help show this help message and exit --log-dir Optional path to the log directory -v, --verbose Enable verbose output --version show program's version number and exit

Examples: lakeperf config.yaml Experiment1 lakeperf /path/to/my-config.yaml "Load Test Production" lakeperf config.json Experiment2 -v --log-dir logs

---

✅ Use Cases

• Performance benchmarking between Databricks and Synapse
• Identifying query latency differences

---

Improvements

• Create Warehouse
• Create APP
• pre and post query