Spaces:
Runtime error
Runtime error
BIM Query Deploy
Claude Opus 4.7 (1M context)
deploy: initial BIM Query API on Hugging Face Spaces
e6be672 Download app/utils/query_optimizer.py from 12mhsssss/bimquery-api: direct link, hf CLI and curl.
- Browser
- Download file 16.9 kB
-
https://huggingface.co/spaces/12mhsssss/bimquery-api/resolve/main/app/utils/query_optimizer.py
- Command line
-
hf download hf://spaces/12mhsssss/bimquery-api/app/utils/query_optimizer.py
-
curl -L -o query_optimizer.py https://huggingface.co/spaces/12mhsssss/bimquery-api/resolve/main/app/utils/query_optimizer.py
16.9 kB
| # 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 <https://www.gnu.org/licenses/>. | |
| """ | |
| 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: | |
| 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""" | |
| 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 | |
| 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 | |
| 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()) | |
| 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 | |
| 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""" | |
| 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 | |
| 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__ | |
| 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""" | |
| 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" | |
| ] |