Skip to content
TheChatArchiveThe Chat Archive
Mistralmistral-medium-2505· Mistral AIHugging Face LMArena Human Preference 140K

IF OBJECTID('tempdb..#EETAnalysis') IS NOT NULL DROP TABLE #EET_Analysis; SELECT v1.*,…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Qwenqwen-max-2025-01-25’s answer.
  1. User

    IF OBJECT_ID('tempdb..#EET_Analysis') IS NOT NULL DROP TABLE #EET_Analysis;

    SELECT v1.*,

    -- Competitor Margin for EET (based on Cost)
    CASE 
        WHEN CostPrice > 0 THEN (Competitor_Price_EUR - CostPrice) / CostPrice
        ELSE NULL
    END AS EET_Margin_CostBased,
    
    -- Target prices to reach Rank 1 and 3
    (
        SELECT MIN(Competitor_Price_EUR)
        FROM vw_ITScope_CompetitiveData v2
        WHERE v2.manufacturerSKU = v1.manufacturerSKU
          AND v2.extraction_date = v1.extraction_date
    ) AS Top1_Price,
    
    (
        SELECT MIN(Competitor_Price_EUR)
        FROM (
            SELECT Competitor_Price_EUR, RANK() OVER (PARTITION BY manufacturerSKU ORDER BY Competitor_Price_EUR ASC) AS rk
            FROM vw_ITScope_CompetitiveData v3
            WHERE v3.manufacturerSKU = v1.manufacturerSKU
              AND v3.extraction_date = v1.extraction_date
        ) x
        WHERE rk <= 3
    ) AS Top3_Price
    

    INTO #EET_Analysis FROM vw_ITScope_CompetitiveData v1 WHERE v1.ItemNo IS NOT NULL AND v1.supplierName = 'EET Deutschland';

    -- Add margins and feasibility to #EET_Analysis SELECT *, CASE WHEN CostPrice > 0 THEN (Top1_Price - CostPrice) / CostPrice ELSE NULL END AS TargetMargin_Top1,

    CASE 
        WHEN CostPrice > 0 THEN (Top3_Price - CostPrice) / CostPrice
        ELSE NULL
    END AS TargetMargin_Top3,
    
    CASE 
        WHEN CostPrice > 0 AND Top3_Price < CostPrice * 1.06 THEN 'Too Expensive for Top3'
        ELSE 'Feasible'
    END AS PriceFeasibility
    

    FROM #EET_Analysis; it takes super long- how we can improve the performance?

  2. Mistral Assistant

    SQL Query Performance Optimization

    The query is slow because it's performing correlated subqueries for each row to calculate the Top1 and Top3 prices. Here are several ways to improve performance:

    Option 1: Use Window Functions with CTEs

    IF OBJECT_ID('tempdb..#EET_Analysis') IS NOT NULL DROP TABLE #EET_Analysis;
    
    WITH RankedCompetitors AS (
        SELECT
            manufacturerSKU,
            extraction_date,
            Competitor_Price_EUR,
            RANK() OVER (PARTITION BY manufacturerSKU, extraction_date ORDER BY Competitor_Price_EUR ASC) AS price_rank
        FROM vw_ITScope_CompetitiveData
        WHERE ItemNo IS NOT NULL
          AND supplierName = 'EET Deutschland'
    ),
    TopPrices AS (
        SELECT
            manufacturerSKU,
            extraction_date,
            MAX(CASE WHEN price_rank = 1 THEN Competitor_Price_EUR END) AS Top1_Price,
            MAX(CASE WHEN price_rank <= 3 THEN Competitor_Price_EUR END) AS Top3_Price
        FROM RankedCompetitors
        GROUP BY manufacturerSKU, extraction_date
    )
    
    SELECT
        v1.*,
        -- Competitor Margin for EET (based on Cost)
        CASE
            WHEN CostPrice > 0 THEN (Competitor_Price_EUR - CostPrice) / CostPrice
            ELSE NULL
        END AS EET_Margin_CostBased,
        t.Top1_Price,
        t.Top3_Price,
        -- Target margins
        CASE
            WHEN v1.CostPrice > 0 THEN (t.Top1_Price - v1.CostPrice) / v1.CostPrice
            ELSE NULL
        END AS TargetMargin_Top1,
        CASE
            WHEN v1.CostPrice > 0 THEN (t.Top3_Price - v1.CostPrice) / v1.CostPrice
            ELSE NULL
        END AS TargetMargin_Top3,
        CASE
            WHEN v1.CostPrice > 0 AND t.Top3_Price < v1.CostPrice * 1.06 THEN 'Too Expensive for Top3'
            ELSE 'Feasible'
        END AS PriceFeasibility
    INTO #EET_Analysis
    FROM vw_ITScope_CompetitiveData v1
    JOIN TopPrices t ON v1.manufacturerSKU = t.manufacturerSKU AND v1.extraction_date = t.extraction_date
    WHERE v1.ItemNo IS NOT NULL
      AND v1.supplierName = 'EET Deutschland';
    
    SELECT * FROM #EET_Analysis;
    

    Option 2: Pre-aggregate the data

    IF OBJECT_ID('tempdb..#EET_Analysis') IS NOT NULL DROP TABLE #EET_Analysis;
    
    -- First create a temporary table with the top prices
    SELECT
        manufacturerSKU,
        extraction_date,
        MIN(Competitor_Price_EUR) AS Top1_Price,
        (
            SELECT MIN(Competitor_Price_EUR)
            FROM (
                SELECT Competitor_Price_EUR,
                       RANK() OVER (PARTITION BY manufacturerSKU, extraction_date ORDER BY Competitor_Price_EUR ASC) AS rk
                FROM vw_ITScope_CompetitiveData
                WHERE ItemNo IS NOT NULL
                  AND supplierName = 'EET Deutschland'
            ) x
            WHERE x.manufacturerSKU = v.manufacturerSKU
              AND x.extraction_date = v.extraction_date
              AND x.rk <= 3
        ) AS Top3_Price
    INTO #TopPrices
    FROM vw_ITScope_CompetitiveData v
    WHERE ItemNo IS NOT NULL
      AND supplierName = 'EET Deutschland'
    GROUP BY manufacturerSKU, extraction_date;
    
    -- Then join with the main data
    SELECT
        v1.*,
        -- Competitor Margin for EET (based on Cost)
        CASE
            WHEN CostPrice > 0 THEN (Competitor_Price_EUR - CostPrice) / CostPrice
            ELSE NULL
        END AS EET_Margin_CostBased,
        t.Top1_Price,
        t.Top3_Price,
        -- Target margins
        CASE
            WHEN v1.CostPrice > 0 THEN (t.Top1_Price - v1.CostPrice) / v1.CostPrice
            ELSE NULL
        END AS TargetMargin_Top1,
        CASE
            WHEN v1.CostPrice > 0 THEN (t.Top3_Price - v1.CostPrice) / v1.CostPrice
            ELSE NULL
        END AS TargetMargin_Top3,
        CASE
            WHEN v1.CostPrice > 0 AND t.Top3_Price < v1.CostPrice * 1.06 THEN 'Too Expensive for Top3'
            ELSE 'Feasible'
        END AS PriceFeasibility
    INTO #EET_Analysis
    FROM vw_ITScope_CompetitiveData v1
    JOIN #TopPrices t ON v1.manufacturerSKU = t.manufacturerSKU AND v1.extraction_date = t.extraction_date
    WHERE v1.ItemNo IS NOT NULL
      AND v1.supplierName = 'EET Deutschland';
    
    SELECT * FROM #EET_Analysis;
    

    Additional Recommendations:

    1. Add appropriate indexes on vw_ITScope_CompetitiveData for:

      • manufacturerSKU
      • extraction_date
      • supplierName
      • A composite index on (manufacturerSKU, extraction_date, Competitor_Price_EUR)
    2. Consider materializing the view if it's frequently used and performance is critical.

    3. Limit the data early by adding more specific filters if possible.

    4. Check if the view itself can be optimized - sometimes views contain inefficient queries.

    The first option (using CTEs) is generally the most efficient approach as it avoids the correlated subqueries and processes the ranking logic in a set-based manner.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.