# Copyright (C) 2026 Mattia Zatelli # # This program is free software: you can redistribute it and/or modify # it under the terms of the GNU Affero General Public License as published by # the Free Software Foundation, either version 3 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU Affero General Public License for more details. # # You should have received a copy of the GNU Affero General Public License # along with this program. If not, see . """ Query Optimization Utilities Provides query optimization patterns for improved performance: - Query result caching - Batch query execution - Index hints - Query plan analysis - Connection pooling optimization """ import logging import hashlib import json from typing import Any, Dict, List, Optional, Callable from datetime import datetime, timedelta from functools import wraps import asyncio from sqlalchemy import text, select from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy.orm import selectinload, joinedload logger = logging.getLogger(__name__) class QueryCache: """In-memory cache for query results with TTL support""" def __init__(self, default_ttl: int = 300): """ Initialize query cache Args: default_ttl: Default time-to-live in seconds (default: 5 minutes) """ self._cache: Dict[str, Dict[str, Any]] = {} self._default_ttl = default_ttl logger.info(f"Initialized QueryCache with TTL={default_ttl}s") def _generate_key(self, query: str, params: Dict[str, Any]) -> str: """Generate cache key from query and parameters""" cache_data = f"{query}:{json.dumps(params, sort_keys=True)}" return hashlib.md5(cache_data.encode()).hexdigest() def get(self, query: str, params: Dict[str, Any] = None) -> Optional[Any]: """ Get cached result if available and not expired Args: query: Query string params: Query parameters Returns: Cached result or None if not found/expired """ params = params or {} key = self._generate_key(query, params) if key in self._cache: cache_entry = self._cache[key] if datetime.now() < cache_entry["expires_at"]: logger.debug(f"Cache HIT for key: {key[:8]}...") return cache_entry["result"] else: # Expired - remove from cache logger.debug(f"Cache EXPIRED for key: {key[:8]}...") del self._cache[key] logger.debug(f"Cache MISS for key: {key[:8]}...") return None def set( self, query: str, params: Dict[str, Any], result: Any, ttl: Optional[int] = None ): """ Store result in cache Args: query: Query string params: Query parameters result: Query result to cache ttl: Time-to-live in seconds (uses default if not specified) """ params = params or {} key = self._generate_key(query, params) ttl = ttl or self._default_ttl self._cache[key] = { "result": result, "expires_at": datetime.now() + timedelta(seconds=ttl), "created_at": datetime.now() } logger.debug(f"Cached result for key: {key[:8]}... (TTL={ttl}s)") def clear(self): """Clear all cached entries""" count = len(self._cache) self._cache.clear() logger.info(f"Cleared {count} cached entries") def get_stats(self) -> Dict[str, Any]: """Get cache statistics""" total = len(self._cache) expired = sum( 1 for entry in self._cache.values() if datetime.now() >= entry["expires_at"] ) return { "total_entries": total, "active_entries": total - expired, "expired_entries": expired, "memory_estimate_mb": total * 0.01 # Rough estimate } # Global cache instance query_cache = QueryCache(default_ttl=300) def cached_query(ttl: int = 300): """ Decorator for caching query results Args: ttl: Cache time-to-live in seconds Example: @cached_query(ttl=600) async def get_elements(session, project_id): return await session.execute(query) """ def decorator(func: Callable) -> Callable: @wraps(func) async def wrapper(*args, **kwargs): # Generate cache key from function name and arguments cache_key = f"{func.__name__}:{args}:{kwargs}" # Check cache cached_result = query_cache.get(cache_key, {}) if cached_result is not None: return cached_result # Execute function result = await func(*args, **kwargs) # Cache result query_cache.set(cache_key, {}, result, ttl=ttl) return result return wrapper return decorator class QueryOptimizer: """Query optimization utilities""" @staticmethod async def batch_fetch_elements( session: AsyncSession, element_ids: List[int], batch_size: int = 100 ) -> List[Any]: """ Fetch elements in batches to avoid large IN clauses Args: session: Database session element_ids: List of element IDs to fetch batch_size: Number of IDs per batch Returns: List of elements """ from app.models.element import Element results = [] for i in range(0, len(element_ids), batch_size): batch_ids = element_ids[i:i + batch_size] query = select(Element).where(Element.id.in_(batch_ids)) batch_result = await session.execute(query) results.extend(batch_result.scalars().all()) logger.info( f"Batch fetched {len(results)} elements in " f"{(len(element_ids) + batch_size - 1) // batch_size} batches" ) return results @staticmethod async def eager_load_relationships( session: AsyncSession, query, relationships: List[str] ): """ Add eager loading for relationships to avoid N+1 queries Args: session: Database session query: SQLAlchemy query relationships: List of relationship names to eager load Returns: Modified query with eager loading """ for relationship in relationships: # Use selectinload for better performance with collections query = query.options(selectinload(relationship)) return query @staticmethod def optimize_query_for_count(query) -> Any: """ Optimize query for count operations Args: query: SQLAlchemy query Returns: Optimized query for counting """ from sqlalchemy import func # Use count(*) which is faster than count(column) return select(func.count()).select_from(query.subquery()) @staticmethod async def parallel_queries( session: AsyncSession, queries: List[Any] ) -> List[Any]: """ Execute multiple queries in parallel Args: session: Database session queries: List of SQLAlchemy queries Returns: List of query results """ tasks = [session.execute(query) for query in queries] results = await asyncio.gather(*tasks) logger.info(f"Executed {len(queries)} queries in parallel") return results @staticmethod def add_index_hints(query, table_name: str, index_name: str): """ Add index hints for query optimization (PostgreSQL) Args: query: SQLAlchemy query table_name: Table name index_name: Index name to hint Returns: Query with index hint """ # Note: PostgreSQL doesn't support index hints like MySQL # This is a placeholder for documentation purposes logger.warning( f"Index hints not supported in PostgreSQL. " f"Consider using EXPLAIN ANALYZE instead." ) return query class BatchProcessor: """Batch processing utilities for large datasets""" @staticmethod async def process_in_batches( session: AsyncSession, query, batch_size: int = 1000, processor: Callable = None ) -> List[Any]: """ Process query results in batches to reduce memory usage Args: session: Database session query: SQLAlchemy query batch_size: Number of records per batch processor: Optional processing function for each batch Returns: Aggregated results from all batches """ results = [] offset = 0 while True: # Fetch batch batch_query = query.limit(batch_size).offset(offset) batch_result = await session.execute(batch_query) batch = batch_result.scalars().all() if not batch: break # Process batch if processor provided if processor: processed = await processor(batch) if asyncio.iscoroutinefunction(processor) else processor(batch) results.extend(processed if isinstance(processed, list) else [processed]) else: results.extend(batch) offset += batch_size logger.debug(f"Processed batch at offset {offset}") logger.info(f"Processed {len(results)} records in batches of {batch_size}") return results @staticmethod async def parallel_batch_processing( session: AsyncSession, queries: List[Any], batch_size: int = 1000, max_workers: int = 5 ) -> List[List[Any]]: """ Process multiple queries in parallel batches Args: session: Database session queries: List of queries to process batch_size: Batch size for each query max_workers: Maximum parallel workers Returns: List of results for each query """ # Limit concurrent tasks semaphore = asyncio.Semaphore(max_workers) async def process_with_semaphore(query): async with semaphore: return await BatchProcessor.process_in_batches( session, query, batch_size ) tasks = [process_with_semaphore(query) for query in queries] results = await asyncio.gather(*tasks) logger.info( f"Processed {len(queries)} queries in parallel " f"with {max_workers} workers" ) return results class PerformanceMonitor: """Monitor and log query performance""" def __init__(self): self._query_times: Dict[str, List[float]] = {} def log_query_time(self, query_name: str, duration: float): """ Log query execution time Args: query_name: Name/identifier for the query duration: Execution time in seconds """ if query_name not in self._query_times: self._query_times[query_name] = [] self._query_times[query_name].append(duration) # Log warning if query is slow if duration > 1.0: logger.warning( f"Slow query detected: {query_name} took {duration:.2f}s" ) def get_statistics(self) -> Dict[str, Dict[str, float]]: """ Get performance statistics for all queries Returns: Dictionary with query statistics """ stats = {} for query_name, times in self._query_times.items(): if times: stats[query_name] = { "count": len(times), "total_time": sum(times), "avg_time": sum(times) / len(times), "min_time": min(times), "max_time": max(times) } return stats def clear_statistics(self): """Clear all performance statistics""" self._query_times.clear() # Global performance monitor performance_monitor = PerformanceMonitor() def monitor_performance(query_name: str = None): """ Decorator to monitor query performance Args: query_name: Optional name for the query (uses function name if not provided) Example: @monitor_performance("get_elements") async def get_elements(session): return await session.execute(query) """ def decorator(func: Callable) -> Callable: name = query_name or func.__name__ @wraps(func) async def wrapper(*args, **kwargs): start_time = datetime.now() try: result = await func(*args, **kwargs) return result finally: duration = (datetime.now() - start_time).total_seconds() performance_monitor.log_query_time(name, duration) return wrapper return decorator class ConnectionPoolOptimizer: """Optimize database connection pool settings""" @staticmethod def get_optimal_pool_size( max_concurrent_requests: int = 100, avg_query_time_ms: float = 50 ) -> Dict[str, int]: """ Calculate optimal connection pool size Args: max_concurrent_requests: Maximum expected concurrent requests avg_query_time_ms: Average query time in milliseconds Returns: Dictionary with recommended pool settings """ # Rule of thumb: pool_size = concurrent_requests * (query_time / request_time) # Assuming 1 request = 2-3 queries on average queries_per_request = 2.5 min_pool_size = max(5, int(max_concurrent_requests * 0.1)) max_pool_size = max(20, int(max_concurrent_requests * queries_per_request * 0.5)) overflow_size = int(max_pool_size * 0.5) return { "pool_size": min_pool_size, "max_overflow": overflow_size, "pool_pre_ping": True, # Verify connections before use "pool_recycle": 3600, # Recycle connections after 1 hour "echo_pool": False # Don't log pool checkouts } # Utility functions for common optimization patterns async def fetch_with_cache( session: AsyncSession, query, cache_key: str, ttl: int = 300 ) -> Any: """ Execute query with caching Args: session: Database session query: SQLAlchemy query cache_key: Cache key ttl: Cache TTL in seconds Returns: Query result (cached or fresh) """ # Check cache cached_result = query_cache.get(cache_key, {}) if cached_result is not None: return cached_result # Execute query result = await session.execute(query) data = result.scalars().all() # Cache result query_cache.set(cache_key, {}, data, ttl=ttl) return data async def fetch_with_pagination( session: AsyncSession, query, page: int = 1, page_size: int = 50 ) -> Dict[str, Any]: """ Execute query with pagination Args: session: Database session query: SQLAlchemy query page: Page number (1-indexed) page_size: Results per page Returns: Dictionary with paginated results and metadata """ from sqlalchemy import func, select # Get total count count_query = select(func.count()).select_from(query.subquery()) total = await session.scalar(count_query) # Calculate pagination offset = (page - 1) * page_size total_pages = (total + page_size - 1) // page_size # Fetch page paginated_query = query.limit(page_size).offset(offset) result = await session.execute(paginated_query) items = result.scalars().all() return { "items": items, "page": page, "page_size": page_size, "total_items": total, "total_pages": total_pages, "has_next": page < total_pages, "has_prev": page > 1 } # Export optimization utilities __all__ = [ "QueryCache", "query_cache", "cached_query", "QueryOptimizer", "BatchProcessor", "PerformanceMonitor", "performance_monitor", "monitor_performance", "ConnectionPoolOptimizer", "fetch_with_cache", "fetch_with_pagination" ]