locked
Min Aggregate function performs too slow RRS feed

  • Question

  • Hi, I am using below query to get the minimum date to aggregate within a subquery. The problem I am having is that runs very slow, almost 10 times slower than without aggregation. I thought aggregating improves the performance, not slows down.  Can anyone suggest why this happening please. Not sure if anything to do with the temp tables which have no unique keys.

    SELECT t1.date, t1.cycleNo, t1.Value1, t1.Value2
    FROM @tmpTable As t1 INNER JOIN @tmpTable2 As t2 ON t1.date = t2.date AND t1.cycleNo = t2.cycleNo
    	INNER JOIN (SELECT MIN(t1.date) As firstDate, t2.cycleNo FROM @tmpTable1 As t1 INNER JOIN @tmpTable2 As t2 
    	ON t1.date = t2.date AND t1.cycleNo = t2.cycleNo GROUP BY t2.cycleNo) As FirstDate
    ON t1.date = FirstDate.firstDate AND t1.cycleNo = FirstDate.cycleNo 

    Thank you

    Monday, June 30, 2014 10:10 PM

Answers

  • Consider applying temporary tables (#temptable) with indexes instead of table variables (@tablevar):

    SELECT t1.date, 
           t1.cycleno, 
           t1.value1, 
           t1.value2 
    FROM   @tmpTable AS t1 
           INNER JOIN @tmpTable2 AS t2 
                   ON t1.date = t2.date 
                      AND t1.cycleno = t2.cycleno 
           INNER JOIN (SELECT Min(t1.date) AS firstDate, 
                              t2.cycleno 
                       FROM   @tmpTable1 AS t1 
                              INNER JOIN @tmpTable2 AS t2 
                                      ON t1.date = t2.date 
                                         AND t1.cycleno = t2.cycleno 
                       GROUP  BY t2.cycleno) AS FirstDate 
                   ON t1.date = FirstDate.firstdate 
                      AND t1.cycleno = FirstDate.cycleno   



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012









    • Edited by Kalman Toth Monday, June 30, 2014 11:13 PM
    • Marked as answer by Alex Teslin Friday, July 4, 2014 11:12 AM
    Monday, June 30, 2014 11:12 PM

All replies

  • Thanks Jose, I will try your example.  My temp tables are declared very simply such as:

    Declare @tempTable1 AS Table (theDate date, cycleNo integer, value1 float, value2 float)

    I thought CTEs are even slower.  I never tried declaring indexes on temp table, would you think might help?

    Monday, June 30, 2014 10:32 PM
  • Consider applying temporary tables (#temptable) with indexes instead of table variables (@tablevar):

    SELECT t1.date, 
           t1.cycleno, 
           t1.value1, 
           t1.value2 
    FROM   @tmpTable AS t1 
           INNER JOIN @tmpTable2 AS t2 
                   ON t1.date = t2.date 
                      AND t1.cycleno = t2.cycleno 
           INNER JOIN (SELECT Min(t1.date) AS firstDate, 
                              t2.cycleno 
                       FROM   @tmpTable1 AS t1 
                              INNER JOIN @tmpTable2 AS t2 
                                      ON t1.date = t2.date 
                                         AND t1.cycleno = t2.cycleno 
                       GROUP  BY t2.cycleno) AS FirstDate 
                   ON t1.date = FirstDate.firstdate 
                      AND t1.cycleno = FirstDate.cycleno   



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012









    • Edited by Kalman Toth Monday, June 30, 2014 11:13 PM
    • Marked as answer by Alex Teslin Friday, July 4, 2014 11:12 AM
    Monday, June 30, 2014 11:12 PM
  • Thanks Jose,  there are only 2 tables used in the query, not 3.  The problem is I can't use temp table declaration with #Table, only with table variables @Table.  The reason is that I am using the query within the look and I can't add more than once into temp table, it throws the "Table is already has been defined..." error.
    Tuesday, July 1, 2014 8:16 AM
  • Ok, for a quick and probably not efficient solution I have created yet another temp table to store the data (without aggregation) first.  Then using this new temp table I am able to aggregate and it's much quicker.  I think this is because I am eliminating the INNER JOINS twice in my original query. 

    Perhaps when I am confident with indexes as well I can add them too in hope that I might get even faster performance.

    Thanks

     
    Tuesday, July 1, 2014 9:14 AM
  • Thanks Kalman, I have followed your suggestion too and replaced table variables with temp tables and it did improve the performance.
    Friday, July 4, 2014 11:12 AM