Need Help Optimizing SQL query

Question

First I would like to say that I'm fairly new to SQL so this might seem like a stupid question. So in this code I receive a Date as a parameter, add 61 minutes to it and check the values in between. Then I Sum the values from each column and store it in another table. The Code is Working just fine, what I would like to know is if there is a better way to do it and how to do it.(Without using so many lines or repetitive code)

Thanks in advance.

alter procedure Contagem
@date datetime

as
begin
    declare
    @Sala1 float,
    @Sala2 float,
    @Sala3 float,
    ...
    @Sala26 float,
    @Sala27 float,
    @Sala28 float,
    @dateplus datetime

    set @Teste = 1
    set @dateplus =  (select DATEADD(MINUTE,61,@date))

    set @Sala1 =  (select sum(Sala_1_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
    set @Sala2 =  (select sum(Sala_2_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
    set @Sala3 =  (select sum(Sala_3_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
    ...
    set @Sala26 =  (select sum(Sala_26_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
    set @Sala27 =  (select sum(Sala_27_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
    set @Sala28 =  (select sum(Sala_28_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)

    Insert into Custos_hora values (@date,@Sala1,@Sala2,@Sala3,@Sala4,@Sala5,@Sala6,@Sala7,@Sala8,@Sala9,@Sala10,@Sala11,@Sala12,@Sala13,@Sala14,@Sala15,@Sala16,@Sala17,@Sala18,@Sala19,@Sala20,@Sala21,@Sala22,@Sala23,@Sala24,@Sala25,@Sala26,@Sala27,@Sala28)
end

Answer 1

You could just hit that table the once rather than 28 times as you are currently doing.

INSERT INTO Custos_hora
SELECT
SUM(Sala_1_Energia)
,SUM(Sala_2_Energia)
,SUM(Sala_3_Energia)
,SUM(Sala_4_Energia)

FROM Energia_15min

WHERE Time_Stamp between @date and @dateplus

It's considered best practice to declare the fields that you're inserting into like this;

INSERT INTO Custos_hora (Field1, Field2, Field3, Field4)
SELECT
SUM(Sala_1_Energia)
,SUM(Sala_2_Energia)
,SUM(Sala_3_Energia)
,SUM(Sala_4_Energia)

FROM Energia_15min

WHERE Time_Stamp between @date and @dateplus

Also, what's the variable @Teste for? it doesn't appear to be being used anywhere. And you don't seem to be declaring @date either.

Answer 2

You can just do the following instead:

Alter Procedure Contagem (@Date DateTime)
As Begin
    Insert  Custos_hora
    Select  @Date,
            Sum(Sala_1_Energia),
            Sum(Sala_2_Energia),
            Sum(Sala_3_Energia),
            Sum(Sala_4_Energia),
            Sum(Sala_5_Energia),
            Sum(Sala_6_Energia),
            Sum(Sala_7_Energia),
            Sum(Sala_8_Energia),
            Sum(Sala_9_Energia),
            Sum(Sala_10_Energia),
            Sum(Sala_11_Energia),
            Sum(Sala_12_Energia),
            Sum(Sala_13_Energia),
            Sum(Sala_14_Energia),
            Sum(Sala_15_Energia),
            Sum(Sala_16_Energia),
            Sum(Sala_17_Energia),
            Sum(Sala_18_Energia),
            Sum(Sala_19_Energia),
            Sum(Sala_20_Energia),
            Sum(Sala_21_Energia),
            Sum(Sala_22_Energia),
            Sum(Sala_23_Energia),
            Sum(Sala_24_Energia),
            Sum(Sala_25_Energia),
            Sum(Sala_26_Energia),
            Sum(Sala_27_Energia),
            Sum(Sala_28_Energia)
    From    Energia_15min
    Where   Time_Stamp Between @Date And DateAdd(Minute, 61, @Date)
End

Answer 3

Multiple SELECT statement will make multiple table scan and increase disk IO and memory utilization. It can be done in single select statement.

    alter procedure Contagem
      @date datetime

      as
      begin


      declare
      @Sala1 float,
      @Sala2 float,
      @Sala3 float,
      @Sala4 float,
      @Sala5 float,
      @Sala6 float,
      @Sala7 float,
      @Sala8 float,
      @Sala9 float,
      @Sala10 float,
      @Sala11 float,
      @Sala12 float,
      @Sala13 float,
      @Sala14 float,
      @Sala15 float,
      @Sala16 float,
      @Sala17 float,
      @Sala18 float,
      @Sala19 float,
      @Sala20 float,
      @Sala21 float,
      @Sala22 float,
      @Sala23 float,
      @Sala24 float,
      @Sala25 float,
      @Sala26 float,
      @Sala27 float,
      @Sala28 float,
      @dateplus datetime

     set @Teste = 1
     set @dateplus =DATEADD(MINUTE,61,@date))

    Insert into Custos_hora
      select sum(Sala_1_Energia),
      sum(Sala_2_Energia),
      sum(Sala_3_Energia),
      sum(Sala_4_Energia),
      sum(Sala_5_Energia),
      sum(Sala_6_Energia),
      sum(Sala_7_Energia),
      sum(Sala_8_Energia),
      sum(Sala_9_Energia),
      sum(Sala_10_Energia),
      sum(Sala_11_Energia),
      sum(Sala_12_Energia),
      sum(Sala_13_Energia),
      sum(Sala_14_Energia),
      sum(Sala_15_Energia),
      sum(Sala_16_Energia),
      sum(Sala_17_Energia),
      sum(Sala_18_Energia),
      sum(Sala_19_Energia),
      sum(Sala_20_Energia),
      sum(Sala_21_Energia),
      sum(Sala_22_Energia),
      sum(Sala_23_Energia),
      sum(Sala_24_Energia),
      sum(Sala_25_Energia),
      sum(Sala_26_Energia),
      sum(Sala_27_Energia),
      sum(Sala_28_Energia) from Energia_15min where Time_Stamp between @date and @dateplus
      end