IF OBJECTID('tempdb..#EETAnalysis') IS NOT NULL DROP TABLE #EET_Analysis; SELECT v1.*,…
- 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_PriceINTO #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 PriceFeasibilityFROM #EET_Analysis; it takes super long- how we can improve the performance?
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:
-
Add appropriate indexes on
vw_ITScope_CompetitiveDatafor:manufacturerSKUextraction_datesupplierName- A composite index on
(manufacturerSKU, extraction_date, Competitor_Price_EUR)
-
Consider materializing the view if it's frequently used and performance is critical.
-
Limit the data early by adding more specific filters if possible.
-
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.
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.