Database Query Performance Optimizer

Build a database query performance optimizer that executes queries with optimized database session settings. The system should configure database session parameters before running queries to improve execution performance.

Requirements

Your implementation should:

1. Accept a database connection string and a SQL query to execute
2. Configure database session parameters before executing the main query to optimize performance
3. Support setting multiple session configuration parameters
4. Load the query results into a dataframe
5. Return both the query results and confirmation that the configuration was applied

Functional Specifications

Input Parameters

• Database connection string (PostgreSQL or MySQL format)
• Main SQL query to execute
• List of session configuration statements to run before the query

Configuration Types to Support

• Memory allocation settings (e.g., work_mem for PostgreSQL)
• Parallel execution settings (e.g., max_parallel_workers_per_gather for PostgreSQL)
• Session behavior settings (e.g., sql_mode for MySQL)

Output

• Query results loaded into a dataframe
• Success indicator showing configuration was applied

Test Cases { .tests }

Test 1: PostgreSQL Memory Configuration { .test }

Input:

• Connection: PostgreSQL database connection string
• Configuration: ["SET work_mem = '256MB'"]
• Query: "SELECT id, name, value FROM test_table WHERE value > 100"

Expected Output:

• Dataframe containing query results with columns: id, name, value
• Configuration successfully applied before query execution

Test 2: PostgreSQL Parallel Workers Configuration { .test }

Input:

• Connection: PostgreSQL database connection string
• Configuration: ["SET max_parallel_workers_per_gather = 4", "SET parallel_setup_cost = 100"]
• Query: "SELECT category, COUNT(*) as count, AVG(amount) as avg_amount FROM transactions GROUP BY category"

Expected Output:

• Dataframe containing aggregated results with columns: category, count, avg_amount
• Both configuration statements applied before query execution

Test 3: MySQL Session Mode Configuration { .test }

Input:

• Connection: MySQL database connection string
• Configuration: ["SET SESSION sql_mode = 'STRICT_ALL_TABLES'"]
• Query: "SELECT product_id, product_name, price FROM products WHERE price > 50.0"

Expected Output:

• Dataframe containing query results with columns: product_id, product_name, price
• Session mode configured before query execution

Implementation Files

• src/query_optimizer.py - Main implementation module
• tests/test_query_optimizer.py - Test suite

Dependencies { .dependencies }

connectorx { .dependency }

Provides fast database-to-dataframe data loading with configuration support.