⚡ Query Optimizer Interactive Demo¶

const optimizationRules = {
  indexing: [
    {
      name: 'Missing Index on Join Column',
      pattern: /JOIN.*ON\s+(\w+)\.(\w+)\s*=\s*(\w+)\.(\w+)/gi,
      check: (query, tables) => {
        // Check if join columns have indexes
        const joinColumns = extractJoinColumns(query);
        return joinColumns.filter(col => !hasIndex(col));
      },
      suggestion: (missingIndexes) => ({
        issue: 'Join columns without indexes',
        impact: 'High - 50-80% performance improvement',
        recommendation: `CREATE INDEX idx_${missingIndexes[0].column} ON ${missingIndexes[0].table}(${missingIndexes[0].column})`,
        priority: 'High'
      })
    },
    {
      name: 'Missing Columnstore Index',
      check: (table) => table.rowCount > 1000000 && !table.hasColumnstoreIndex,
      suggestion: {
        issue: 'Large table without columnstore index',
        impact: 'High - 10-20x query performance',
        recommendation: 'CREATE CLUSTERED COLUMNSTORE INDEX ON table_name',
        priority: 'Critical'
      }
    }
  ],

  distribution: [
    {
      name: 'Suboptimal Distribution for Joins',
      check: (query) => {
        const joins = extractJoins(query);
        return joins.filter(join =>
          !isColocated(join.leftTable, join.rightTable, join.column)
        );
      },
      suggestion: (joins) => ({
        issue: 'Data movement during join operations',
        impact: 'High - Eliminates shuffle operations',
        recommendation: `Redistribute tables on join key: ${joins[0].column}`,
        priority: 'High'
      })
    }
  ],

  partitioning: [
    {
      name: 'Full Table Scan on Partitioned Table',
      pattern: /WHERE.*date.*>|<|BETWEEN/i,
      check: (query, table) => {
        return table.isPartitioned && !hasPartitionFilter(query);
      },
      suggestion: {
        issue: 'Query scans all partitions',
        impact: 'Medium - 30-50% reduction in data scanned',
        recommendation: 'Add partition column to WHERE clause',
        priority: 'Medium'
      }
    }
  ]
};

// Before optimization
const slowQuery = {
  sql: `
    SELECT c.CustomerName, SUM(s.Revenue)
    FROM Sales s
    JOIN Customers c ON s.CustomerID = c.CustomerID
    WHERE s.OrderDate >= '2024-01-01'
    GROUP BY c.CustomerName
    ORDER BY SUM(s.Revenue) DESC
  `,
  metrics: {
    executionTime: 45.2, // seconds
    dataScanned: 1250.5, // GB
    estimatedCost: 6.25, // USD
    dataMoved: 850.3 // GB (shuffle)
  }
};

// After optimization
const optimizedQuery = {
  sql: `
    WITH SalesFiltered AS (
      SELECT CustomerID, Revenue
      FROM Sales
      WHERE OrderDate >= '2024-01-01'
        AND OrderDate < '2024-02-01' -- Partition elimination
    )
    SELECT
      c.CustomerName,
      SUM(s.Revenue) AS TotalRevenue
    FROM SalesFiltered s
    INNER JOIN Customers c ON s.CustomerID = c.CustomerID
    GROUP BY c.CustomerName
    ORDER BY TotalRevenue DESC
  `,
  metrics: {
    executionTime: 8.3, // seconds (82% improvement)
    dataScanned: 125.2, // GB (90% reduction)
    estimatedCost: 0.63, // USD (90% savings)
    dataMoved: 0 // GB (colocated join)
  },
  improvements: [
    'Added partition filter for elimination',
    'Used CTE for better readability',
    'Colocated join eliminates data movement',
    'Statistics up to date'
  ]
};

const executionPlanAnalyzer = {
  analyzeNode: (node) => {
    const issues = [];

    if (node.type === 'TableScan' && node.rowsProcessed > 1000000) {
      issues.push({
        severity: 'warning',
        message: 'Full table scan on large table',
        suggestion: 'Add WHERE clause or create index'
      });
    }

    if (node.type === 'DataMovement' && node.dataSize > 100) { // GB
      issues.push({
        severity: 'critical',
        message: 'Large data movement operation',
        suggestion: 'Check distribution strategy'
      });
    }

    if (node.estimatedCost > 1000) {
      issues.push({
        severity: 'warning',
        message: 'High cost operation',
        suggestion: 'Review and optimize this step'
      });
    }

    return issues;
  }
};

const performanceIssues = {
  'Data Skew': {
    symptom: 'Some workers finish much faster than others',
    detection: 'Check distribution column cardinality',
    solution: 'Choose better distribution column or use ROUND_ROBIN'
  },
  'Parameter Sniffing': {
    symptom: 'Query fast sometimes, slow other times',
    detection: 'Compare execution plans with different parameters',
    solution: 'Use OPTION(RECOMPILE) or optimize for unknown'
  },
  'Missing Statistics': {
    symptom: 'Poor cardinality estimates in execution plan',
    detection: 'Check sys.stats_date',
    solution: 'CREATE STATISTICS or UPDATE STATISTICS'
  }
};