query-performance-analyzer 0.4.0.dev1

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:

• 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": {
            "SYNAPSE_SERVER": "myysynapse-ondemand.sql.azuresynapse.net",
            "SYNAPSE_DATABASE": "my_database",
            "SOURCE": "Synapse"
        },
        "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:
    SYNAPSE_SERVER: "myysynapse-ondemand.sql.azuresynapse.net"
    SYNAPSE_DATABASE: "my_database"
    SOURCE: "Synapse"
  
  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": {
      "SYNAPSE_SERVER": "myysynapse-ondemand.sql.azuresynapse.net",
      "SYNAPSE_DATABASE": "my_database",
      "SOURCE": "Synapse"
    },
    "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

Use the new structured runner for custom experiments. See also samples.

# 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)

---

✅ Use Cases

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

---

📝 Notes

• Requires Azure credentials (DefaultAzureCredential) for Synapse authentication
• Requires Databricks SQL Connector

Improvements

• Create Warehouse
• Create APP
• Command line support
• pre and post query