sql - 无论如何，以提高查询或任何替代方式的性能

Question

我已经存储了要优化查询的过程。我需要在同一个临时表中设置三种不同类型的数据。以下是我的查询

SET @Counter = -3;      
WHILE (@Counter <=-1)      
 BEGIN    
 insert into #tempTable (col1, col2,amount)
 select col1,col2,
  CASE 
      WHEN @Counter  = -2 THEN MAX(col3) --sample actual is different
      WHEN @Counter  = -3 then sum(Col3)
      WHEN @Counter  = -1 col3
    SET @Counter = @Counter + 1;      
      from   #tempTable where amount>100 
      group by col1,col2,amount
END

任何优化它的方法

标签： sqlsql-serverdatabase-performance

Answer 1

为什么不简单地使用UNION ALL？：

INSERT INTO #tempTable (col1, col2, amount)
     SELECT col1, col2, MAX(col3)
     FROM #tempTable 
     WHERE amount > 100
     GROUP BY col1, col2
     UNION ALL
     SELECT col1, col2, SUM(col3)
     FROM #tempTable 
     WHERE amount > 100
     GROUP BY col1, col2 
     UNION ALL
     SELECT col1, col2, col3
     FROM #tempTable 
     WHERE amount > 100;