298 lines
10 KiB
Python
298 lines
10 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Query performance monitoring script for VFX Project Management System.
|
|
Analyzes query execution plans and provides performance insights.
|
|
"""
|
|
|
|
import sqlite3
|
|
import sys
|
|
import time
|
|
from pathlib import Path
|
|
|
|
def monitor_query_performance():
|
|
"""Monitor and analyze query performance for key operations."""
|
|
|
|
# Database path
|
|
db_path = Path(__file__).parent / "vfx_project_management.db"
|
|
|
|
if not db_path.exists():
|
|
print(f"Database file not found at {db_path}")
|
|
return False
|
|
|
|
try:
|
|
# Connect to database
|
|
conn = sqlite3.connect(str(db_path))
|
|
cursor = conn.cursor()
|
|
|
|
print("=== Query Performance Monitoring ===\n")
|
|
|
|
# Define key queries to test
|
|
test_queries = [
|
|
{
|
|
"name": "Get active tasks by user",
|
|
"query": """
|
|
SELECT t.id, t.name, t.status, t.task_type
|
|
FROM tasks t
|
|
WHERE t.assigned_user_id = 1
|
|
AND t.deleted_at IS NULL
|
|
ORDER BY t.created_at DESC
|
|
LIMIT 50
|
|
""",
|
|
"description": "Common query for user task dashboard"
|
|
},
|
|
{
|
|
"name": "Get shots by episode (non-deleted)",
|
|
"query": """
|
|
SELECT s.id, s.name, s.status, s.frame_start, s.frame_end
|
|
FROM shots s
|
|
WHERE s.episode_id = 1
|
|
AND s.deleted_at IS NULL
|
|
ORDER BY s.name
|
|
""",
|
|
"description": "Common query for shot browser"
|
|
},
|
|
{
|
|
"name": "Get assets by project (non-deleted)",
|
|
"query": """
|
|
SELECT a.id, a.name, a.category, a.status
|
|
FROM assets a
|
|
WHERE a.project_id = 1
|
|
AND a.deleted_at IS NULL
|
|
ORDER BY a.category, a.name
|
|
""",
|
|
"description": "Common query for asset browser"
|
|
},
|
|
{
|
|
"name": "Get task submissions with reviews",
|
|
"query": """
|
|
SELECT s.id, s.file_name, s.version_number, s.submitted_at,
|
|
r.decision, r.feedback
|
|
FROM submissions s
|
|
LEFT JOIN reviews r ON s.id = r.submission_id AND r.deleted_at IS NULL
|
|
WHERE s.task_id = 1
|
|
AND s.deleted_at IS NULL
|
|
ORDER BY s.version_number DESC
|
|
""",
|
|
"description": "Common query for task detail panel"
|
|
},
|
|
{
|
|
"name": "Get project tasks with status filter",
|
|
"query": """
|
|
SELECT t.id, t.name, t.task_type, t.status,
|
|
u.first_name, u.last_name,
|
|
COALESCE(s.name, a.name) as parent_name
|
|
FROM tasks t
|
|
LEFT JOIN users u ON t.assigned_user_id = u.id
|
|
LEFT JOIN shots s ON t.shot_id = s.id AND s.deleted_at IS NULL
|
|
LEFT JOIN assets a ON t.asset_id = a.id AND a.deleted_at IS NULL
|
|
WHERE t.project_id = 1
|
|
AND t.status IN ('in_progress', 'pending_review')
|
|
AND t.deleted_at IS NULL
|
|
ORDER BY t.created_at DESC
|
|
""",
|
|
"description": "Complex query for project task overview"
|
|
},
|
|
{
|
|
"name": "Get recent activity feed",
|
|
"query": """
|
|
SELECT a.type, a.description, a.created_at,
|
|
u.first_name, u.last_name
|
|
FROM activities a
|
|
LEFT JOIN users u ON a.user_id = u.id
|
|
WHERE a.project_id = 1
|
|
ORDER BY a.created_at DESC
|
|
LIMIT 20
|
|
""",
|
|
"description": "Common query for activity feed"
|
|
},
|
|
{
|
|
"name": "Get user workload summary",
|
|
"query": """
|
|
SELECT u.id, u.first_name, u.last_name,
|
|
COUNT(t.id) as task_count,
|
|
COUNT(CASE WHEN t.status = 'in_progress' THEN 1 END) as active_tasks
|
|
FROM users u
|
|
LEFT JOIN tasks t ON u.id = t.assigned_user_id AND t.deleted_at IS NULL
|
|
WHERE u.role = 'artist'
|
|
GROUP BY u.id, u.first_name, u.last_name
|
|
ORDER BY task_count DESC
|
|
""",
|
|
"description": "Query for workload management"
|
|
}
|
|
]
|
|
|
|
print("Testing query performance...\n")
|
|
|
|
for test in test_queries:
|
|
print(f"Query: {test['name']}")
|
|
print(f"Description: {test['description']}")
|
|
|
|
# Get query plan
|
|
explain_query = f"EXPLAIN QUERY PLAN {test['query']}"
|
|
cursor.execute(explain_query)
|
|
plan = cursor.fetchall()
|
|
|
|
print("Execution Plan:")
|
|
for step in plan:
|
|
print(f" {step[0]}: {step[3]}")
|
|
|
|
# Measure execution time
|
|
start_time = time.time()
|
|
cursor.execute(test['query'])
|
|
results = cursor.fetchall()
|
|
end_time = time.time()
|
|
|
|
execution_time = (end_time - start_time) * 1000 # Convert to milliseconds
|
|
|
|
print(f"Execution Time: {execution_time:.2f}ms")
|
|
print(f"Result Count: {len(results)}")
|
|
|
|
# Check if indexes are being used
|
|
plan_text = " ".join([step[3] for step in plan])
|
|
if "USING INDEX" in plan_text:
|
|
print("✓ Index usage detected")
|
|
else:
|
|
print("⚠ No index usage detected")
|
|
|
|
print("-" * 60)
|
|
|
|
return True
|
|
|
|
except sqlite3.Error as e:
|
|
print(f"Database error: {e}")
|
|
return False
|
|
except Exception as e:
|
|
print(f"Unexpected error: {e}")
|
|
return False
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
|
|
def analyze_index_usage():
|
|
"""Analyze which indexes are being used effectively."""
|
|
|
|
db_path = Path(__file__).parent / "vfx_project_management.db"
|
|
|
|
try:
|
|
conn = sqlite3.connect(str(db_path))
|
|
cursor = conn.cursor()
|
|
|
|
print("\n=== Index Usage Analysis ===\n")
|
|
|
|
# Get all indexes
|
|
cursor.execute("""
|
|
SELECT name, tbl_name
|
|
FROM sqlite_master
|
|
WHERE type = 'index'
|
|
AND name NOT LIKE 'sqlite_%'
|
|
ORDER BY tbl_name, name
|
|
""")
|
|
indexes = cursor.fetchall()
|
|
|
|
print(f"Total custom indexes: {len(indexes)}")
|
|
print("\nIndexes by table:")
|
|
|
|
current_table = None
|
|
for index_name, table_name in indexes:
|
|
if table_name != current_table:
|
|
print(f"\n{table_name}:")
|
|
current_table = table_name
|
|
print(f" - {index_name}")
|
|
|
|
# Analyze soft deletion indexes
|
|
print("\n=== Soft Deletion Index Analysis ===")
|
|
|
|
soft_deletion_tables = ['shots', 'assets', 'tasks', 'submissions',
|
|
'task_attachments', 'production_notes', 'reviews']
|
|
|
|
for table in soft_deletion_tables:
|
|
cursor.execute(f"PRAGMA index_list({table})")
|
|
table_indexes = cursor.fetchall()
|
|
|
|
soft_del_indexes = [idx for idx in table_indexes if 'not_deleted' in idx[1]]
|
|
|
|
print(f"{table}: {len(soft_del_indexes)} soft deletion indexes")
|
|
for idx in soft_del_indexes:
|
|
print(f" - {idx[1]}")
|
|
|
|
return True
|
|
|
|
except sqlite3.Error as e:
|
|
print(f"Database error: {e}")
|
|
return False
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
|
|
def benchmark_soft_deletion_queries():
|
|
"""Benchmark queries with and without soft deletion filtering."""
|
|
|
|
db_path = Path(__file__).parent / "vfx_project_management.db"
|
|
|
|
try:
|
|
conn = sqlite3.connect(str(db_path))
|
|
cursor = conn.cursor()
|
|
|
|
print("\n=== Soft Deletion Query Benchmarks ===\n")
|
|
|
|
# Test queries with soft deletion filtering
|
|
benchmark_queries = [
|
|
{
|
|
"name": "Tasks with soft deletion filter",
|
|
"query": "SELECT COUNT(*) FROM tasks WHERE deleted_at IS NULL"
|
|
},
|
|
{
|
|
"name": "Tasks without filter (all records)",
|
|
"query": "SELECT COUNT(*) FROM tasks"
|
|
},
|
|
{
|
|
"name": "Shots with soft deletion filter",
|
|
"query": "SELECT COUNT(*) FROM shots WHERE deleted_at IS NULL"
|
|
},
|
|
{
|
|
"name": "Shots without filter (all records)",
|
|
"query": "SELECT COUNT(*) FROM shots"
|
|
},
|
|
{
|
|
"name": "Assets with soft deletion filter",
|
|
"query": "SELECT COUNT(*) FROM assets WHERE deleted_at IS NULL"
|
|
},
|
|
{
|
|
"name": "Assets without filter (all records)",
|
|
"query": "SELECT COUNT(*) FROM assets"
|
|
}
|
|
]
|
|
|
|
for benchmark in benchmark_queries:
|
|
# Measure execution time
|
|
start_time = time.time()
|
|
cursor.execute(benchmark['query'])
|
|
result = cursor.fetchone()[0]
|
|
end_time = time.time()
|
|
|
|
execution_time = (end_time - start_time) * 1000
|
|
|
|
print(f"{benchmark['name']}: {result} records ({execution_time:.2f}ms)")
|
|
|
|
return True
|
|
|
|
except sqlite3.Error as e:
|
|
print(f"Database error: {e}")
|
|
return False
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
|
|
if __name__ == "__main__":
|
|
print("Query Performance Monitoring Tool")
|
|
print("=" * 50)
|
|
|
|
success = monitor_query_performance()
|
|
|
|
if success:
|
|
analyze_index_usage()
|
|
benchmark_soft_deletion_queries()
|
|
print("\nSUCCESS: Query performance analysis completed!")
|
|
else:
|
|
print("\n❌ Query performance analysis failed!")
|
|
sys.exit(1) |