Validating Phase 3 Performance Improvements¶

# Add to Settings class
SQL_ECHO: bool = True  # Enable SQL query logging

"""
Manual validation script for Phase 3 performance improvements.
Run with: poetry run python dev_tests/manual/validate_performance.py
"""
import asyncio
import time
from datetime import datetime, UTC
from uuid import uuid4

from sqlalchemy import create_engine, event
from sqlalchemy.orm import Session

from backend.rag_solution.models.conversation_session import ConversationSession
from backend.rag_solution.models.conversation_message import ConversationMessage
from backend.rag_solution.repository.conversation_repository import ConversationRepository
from backend.rag_solution.services.conversation_service import ConversationService
from backend.rag_solution.core.config import settings


def count_queries(engine):
    """Count SQL queries using SQLAlchemy event listener."""
    query_count = {"count": 0, "queries": []}

    @event.listens_for(engine, "before_cursor_execute")
    def receive_before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
        query_count["count"] += 1
        query_count["queries"].append(statement)

    return query_count


async def validate_performance():
    """Validate Phase 3 performance improvements."""

    # Setup database
    engine = create_engine(settings.COLLECTIONDB_URL, echo=True)  # echo=True shows SQL
    db = Session(engine)

    # Setup services
    repository = ConversationRepository(db)
    service = ConversationService(db, settings, repository, question_service=None)

    # Create test data (user with 10 sessions, each with 50 messages)
    user_id = uuid4()
    print("\n" + "="*80)
    print("CREATING TEST DATA: 10 sessions with 50 messages each")
    print("="*80)

    for i in range(10):
        session = ConversationSession(
            user_id=user_id,
            collection_id=uuid4(),
            name=f"Test Session {i}",
            created_at=datetime.now(UTC)
        )
        db.add(session)
        db.flush()

        for j in range(50):
            message = ConversationMessage(
                session_id=session.id,
                role="user" if j % 2 == 0 else "assistant",
                content=f"Message {j}",
                created_at=datetime.now(UTC)
            )
            db.add(message)

    db.commit()
    print(f"✅ Created 10 sessions with 500 total messages\n")

    # === VALIDATION 1: Query Count ===
    print("\n" + "="*80)
    print("VALIDATION 1: QUERY COUNT")
    print("="*80)

    query_counter = count_queries(engine)

    start_time = time.perf_counter()
    sessions = await service.list_sessions(user_id)
    end_time = time.perf_counter()

    query_count = query_counter["count"]
    response_time_ms = (end_time - start_time) * 1000

    print(f"\n📊 RESULTS:")
    print(f"   Sessions retrieved: {len(sessions)}")
    print(f"   Total queries executed: {query_count}")
    print(f"   Response time: {response_time_ms:.2f}ms")

    # === VALIDATION 2: Performance Metrics ===
    print("\n" + "="*80)
    print("VALIDATION 2: PERFORMANCE METRICS")
    print("="*80)

    expected_queries = 3  # Main query + 2 Collection relationship queries
    expected_response_time_ms = 5

    query_reduction_pct = ((54 - query_count) / 54) * 100
    response_time_improvement_pct = ((156 - response_time_ms) / 156) * 100

    print(f"\n📈 PERFORMANCE IMPROVEMENTS:")
    print(f"   Query reduction: {54} → {query_count} ({query_reduction_pct:.1f}% reduction)")
    print(f"   Response time: 156ms → {response_time_ms:.2f}ms ({response_time_improvement_pct:.1f}% improvement)")
    print(f"\n💡 NOTE: 3 queries (not 1) due to Collection's lazy-loaded relationships:")

    # === VALIDATION 3: Eager Loading Verification ===
    print("\n" + "="*80)
    print("VALIDATION 3: EAGER LOADING VERIFICATION")
    print("="*80)

    # Reset query counter
    query_counter["count"] = 0

    # Access relationships - should NOT trigger additional queries if eager loaded
    for session in sessions:
        _ = session.messages  # Access messages relationship
        _ = session.summaries  # Access summaries relationship

    additional_queries = query_counter["count"]

    print(f"\n🔍 EAGER LOADING CHECK:")
    print(f"   Queries after accessing relationships: {additional_queries}")
    print(f"   Expected: 0 (eager loading prevents lazy loading)")

    if additional_queries == 0:
        print(f"   ✅ PASS: Eager loading is working correctly!")
    else:
        print(f"   ❌ FAIL: Lazy loading detected ({additional_queries} additional queries)")

    # === FINAL VERDICT ===
    print("\n" + "="*80)
    print("FINAL VALIDATION RESULTS")
    print("="*80)

    all_passed = True

    if query_count == expected_queries:
        print(f"✅ Query count: PASS ({query_count} query)")
    else:
        print(f"❌ Query count: FAIL (expected {expected_queries}, got {query_count})")
        all_passed = False

    if response_time_ms < expected_response_time_ms:
        print(f"✅ Response time: PASS ({response_time_ms:.2f}ms < {expected_response_time_ms}ms)")
    else:
        print(f"⚠️  Response time: ACCEPTABLE ({response_time_ms:.2f}ms, target <{expected_response_time_ms}ms)")

    if additional_queries == 0:
        print(f"✅ Eager loading: PASS (no lazy loading)")
    else:
        print(f"❌ Eager loading: FAIL ({additional_queries} lazy queries)")
        all_passed = False

    if query_reduction_pct >= 95:
        print(f"✅ Query reduction: PASS ({query_reduction_pct:.1f}% reduction)")
    else:
        print(f"❌ Query reduction: FAIL ({query_reduction_pct:.1f}% reduction, expected ≥95%)")
        all_passed = False

    if response_time_improvement_pct >= 90:
        print(f"✅ Response time improvement: PASS ({response_time_improvement_pct:.1f}% improvement)")
    else:
        print(f"⚠️  Response time improvement: ACCEPTABLE ({response_time_improvement_pct:.1f}% improvement)")

    print("\n" + "="*80)
    if all_passed:
        print("🎉 ALL VALIDATIONS PASSED!")
    else:
        print("⚠️  SOME VALIDATIONS FAILED - SEE ABOVE")
    print("="*80 + "\n")

    # Cleanup
    db.close()


if __name__ == "__main__":
    asyncio.run(validate_performance())

"""
SQL profiling script to analyze query execution plans.
Shows the actual SQL queries and their execution plans.
"""
from sqlalchemy import create_engine, text
from backend.rag_solution.core.config import settings


def profile_queries():
    """Profile SQL queries with EXPLAIN ANALYZE."""

    engine = create_engine(settings.COLLECTIONDB_URL)

    with engine.connect() as conn:
        # Get a sample user_id
        result = conn.execute(text("SELECT id FROM users LIMIT 1"))
        user_id = result.scalar()

        if not user_id:
            print("❌ No users found. Create test data first.")
            return

        print("\n" + "="*80)
        print("SQL QUERY EXECUTION PLAN")
        print("="*80)

        # The actual query used by ConversationRepository.get_sessions_by_user()
        query = text("""
        EXPLAIN ANALYZE
        SELECT
            conversation_sessions.*,
            conversation_messages.*,
            conversation_summaries.*
        FROM conversation_sessions
        LEFT OUTER JOIN conversation_messages
            ON conversation_sessions.id = conversation_messages.session_id
        LEFT OUTER JOIN conversation_summaries
            ON conversation_sessions.id = conversation_summaries.session_id
        WHERE conversation_sessions.user_id = :user_id
        ORDER BY conversation_sessions.updated_at DESC
        LIMIT 50
        """)

        result = conn.execute(query, {"user_id": user_id})

        print("\nQUERY EXECUTION PLAN:")
        for row in result:
            print(f"  {row[0]}")

        print("\n" + "="*80)
        print("KEY METRICS FROM EXECUTION PLAN:")
        print("="*80)
        print("Look for:")
        print("  • 'Nested Loop' or 'Hash Join' - indicates JOINs are efficient")
        print("  • 'Index Scan' - indicates indexes are being used")
        print("  • Low 'actual time' - indicates fast execution")
        print("  • Single query - no N+1 problem")
        print("="*80 + "\n")


if __name__ == "__main__":
    profile_queries()

"""
Benchmark comparison: Old (direct ORM) vs New (unified repository).
"""
import asyncio
import time
from statistics import mean, median
from uuid import uuid4

from sqlalchemy import create_engine, func
from sqlalchemy.orm import Session

from backend.rag_solution.models.conversation_session import ConversationSession
from backend.rag_solution.repository.conversation_repository import ConversationRepository
from backend.rag_solution.core.config import settings


def benchmark_old_approach(db: Session, user_id):
    """Old approach: Direct ORM with N+1 problem."""
    start = time.perf_counter()

    # Direct ORM query
    sessions = db.query(ConversationSession).filter_by(user_id=user_id).limit(50).all()

    # N+1 problem: separate query for each session's message count
    for session in sessions:
        message_count = db.query(func.count(ConversationMessage.id)).filter_by(
            session_id=session.id
        ).scalar()
        session.message_count = message_count  # Would trigger 50+ queries

    end = time.perf_counter()
    return (end - start) * 1000  # ms


async def benchmark_new_approach(repository: ConversationRepository, user_id):
    """New approach: Unified repository with eager loading."""
    start = time.perf_counter()

    # Single query with eager loading
    sessions = await repository.get_sessions_by_user(user_id, limit=50)

    end = time.perf_counter()
    return (end - start) * 1000  # ms


async def main():
    """Run benchmark comparison."""
    engine = create_engine(settings.COLLECTIONDB_URL)
    db = Session(engine)
    repository = ConversationRepository(db)

    user_id = uuid4()

    # Create test data
    print("Creating test data...")
    for i in range(50):
        session = ConversationSession(user_id=user_id, collection_id=uuid4())
        db.add(session)
    db.commit()

    # Run benchmarks
    print("\nRunning benchmarks (10 iterations each)...")

    old_times = []
    for i in range(10):
        time_ms = benchmark_old_approach(db, user_id)
        old_times.append(time_ms)
        print(f"  Old approach iteration {i+1}: {time_ms:.2f}ms")

    new_times = []
    for i in range(10):
        time_ms = await benchmark_new_approach(repository, user_id)
        new_times.append(time_ms)
        print(f"  New approach iteration {i+1}: {time_ms:.2f}ms")

    # Results
    print("\n" + "="*80)
    print("BENCHMARK RESULTS")
    print("="*80)
    print(f"\nOld Approach (Direct ORM with N+1):")
    print(f"  Mean: {mean(old_times):.2f}ms")
    print(f"  Median: {median(old_times):.2f}ms")
    print(f"  Min: {min(old_times):.2f}ms")
    print(f"  Max: {max(old_times):.2f}ms")

    print(f"\nNew Approach (Unified Repository with Eager Loading):")
    print(f"  Mean: {mean(new_times):.2f}ms")
    print(f"  Median: {median(new_times):.2f}ms")
    print(f"  Min: {min(new_times):.2f}ms")
    print(f"  Max: {max(new_times):.2f}ms")

    improvement = ((mean(old_times) - mean(new_times)) / mean(old_times)) * 100
    speedup = mean(old_times) / mean(new_times)

    print(f"\n📊 Performance Improvement: {improvement:.1f}%")
    print(f"⚡ Speedup: {speedup:.1f}x faster")
    print("="*80 + "\n")

    db.close()


if __name__ == "__main__":
    asyncio.run(main())